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
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.
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>
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.