BookmarkSubscribeRSS Feed
vallsas
Pyrite | Level 9

Hi All,

I am using key value object and sector having the product count(measure) so it will display the name and count some sector does not have any count in that case sector name not appearing and also no count as zero.

 

Thanks in advance

1 REPLY 1
acordes
Rhodochrosite | Level 12

If the row does not exist, there's no direct way to solve it directly I guess.

BUT you could produce a cartesian product inside Visual Analytics by using the join option several times. 

 

The grey cross table is the crossings in your table, the yellow ones are aggregated tables that are joined twice to produce the desired output (green). 

The last joined table creates a calculated item if aggregated frequency is missing then 0, else aggregated frequency. 

 

pic.png

here the BIRD code from the VA (ctrl + ALT + b)

       <DataSource label="ALL_MIGRATION_CT2_AGGREGATED (1)" type="relational" name="ds198">
            <GeneratedResource type="standalone" resource="ge197" generator="dd194" source="ds7" lifetime="executor" externalReferenceName="cas-shared-defaultPublicALL_MIGRATION_CT2producto"/>
            <BusinessItemFolder>
                <GeneratedDataItem root="bi196" name="bi200" label="producto" xref="producto" usage="categorical" format="$."/>
                <PredefinedDataItem calculation="totalCount" name="bi201" label="Frequency" usage="quantitative" format="COMMA12."/>
                <PredefinedDataItem calculation="totalCountPercent" name="bi202" label="Frequency Percent" usage="quantitative" format="PERCENT20.2"/>
                <GeneratedDataItem root="bi214" name="bi217" label="join_criteria" xref="calculation" usage="categorical" format="$."/>
            </BusinessItemFolder>
        </DataSource>
        <DataSource label="ALL_MIGRATION_CT2_AGGREGATED" type="relational" name="ds185">
            <GeneratedResource type="standalone" resource="ge175" generator="dd172" source="ds7" lifetime="executor" externalReferenceName="cas-shared-defaultPublicALL_MIGRATION_CT2productofrom_brand"/>
            <BusinessItemFolder>
                <GeneratedDataItem root="bi174" name="bi187" label="producto" xref="producto" usage="categorical" format="$."/>
                <GeneratedDataItem root="bi176" name="bi188" label="from_brand" xref="from_brand" usage="categorical" format="$."/>
                <GeneratedDataItem root="bi183" name="bi189" label="Aggregated Frequency" xref="calculation" usage="quantitative" format="COMMA12." aggregation="sum"/>
                <PredefinedDataItem calculation="totalCount" name="bi190" label="Frequency" usage="quantitative" format="COMMA12."/>
                <PredefinedDataItem calculation="totalCountPercent" name="bi191" label="Frequency Percent" usage="quantitative" format="PERCENT20.2"/>
                <GeneratedDataItem root="bi208" name="bi211" label="join_criteria" xref="calculation1" usage="categorical" format="$."/>
                <GeneratedDataItem root="bi488" name="bi491" label="brand" xref="calculation2" usage="categorical" format="$."/>
            </BusinessItemFolder>
        </DataSource>
        <DataSource label="Data_Join (2)" type="relational" name="ds402">
            <GeneratedResource type="standalone" resource="ge498" generator="dd495" lifetime="executor" sources="ds185 ds198" externalReferenceName="cas-shared-defaultPublicALL_MIGRATION_CT2calculation1from_brandcalculation2calculationproducto"/>
            <BusinessItemFolder>
                <GeneratedDataItem root="bi398" name="bi404" label="from_brand" xref="from_brand" usage="categorical" format="$."/>
                <GeneratedDataItem root="bi400" name="bi405" label="producto" xref="producto" usage="categorical" format="$."/>
                <PredefinedDataItem calculation="totalCount" name="bi406" label="Frequency" usage="quantitative" format="COMMA12."/>
                <PredefinedDataItem calculation="totalCountPercent" name="bi407" label="Frequency Percent" usage="quantitative" format="PERCENT20.2"/>
                <GeneratedDataItem root="bi501" name="bi506" label="brand" xref="calculation2" usage="categorical" format="$."/>
            </BusinessItemFolder>
        </DataSource>
        <DataSource label="Data_Join (3)" type="relational" name="ds429">
            <GeneratedResource type="standalone" resource="ge513" generator="dd510" lifetime="executor" sources="ds402 ds185" externalReferenceName="cas-shared-defaultPublicALL_MIGRATION_CT2calculation2productofrom_brandcalculation2productocalculation"/>
            <BusinessItemFolder>
                <GeneratedDataItem root="bi423" name="bi431" label="from_brand" xref="from_brand" usage="categorical" format="$."/>
                <GeneratedDataItem root="bi424" name="bi432" label="producto" xref="producto" usage="categorical" format="$."/>
                <GeneratedDataItem root="bi427" name="bi433" label="Aggregated Frequency" xref="calculation" usage="quantitative" format="COMMA12." aggregation="average"/>
                <PredefinedDataItem calculation="totalCount" name="bi434" label="Frequency" usage="quantitative" format="COMMA12."/>
                <PredefinedDataItem calculation="totalCountPercent" name="bi435" label="Frequency Percent" usage="quantitative" format="PERCENT20.2"/>
                <CalculatedItem dataType="double" name="bi479" label="want variable" usage="quantitative" format="COMMA12." aggregation="average">
                    <Expression>cond(ismissing(${bi433,raw}),0,${bi433,raw})</Expression>
                </CalculatedItem>
                <GeneratedDataItem root="bi514" name="bi522" label="brand" xref="calculation2" usage="categorical" format="$."/>
            </BusinessItemFolder>
        </DataSource>
    </DataSources>

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 449 views
  • 1 like
  • 2 in conversation