Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to pass the dimension values of qlik table to a set analysis expression in the same table.
This needs to be accomplished in set analysis .
Data | ||||||||
Org_code | Org_name | Sales | Org_secondary_Code | |||||
1106 | XYZ | 106 | 1106 | |||||
1102 | XCV | 30 | 1106 | |||||
1106 | UTY | 40 | 1106 | |||||
1106 | IOP | 40 | 1104 | |||||
1101 | HGF | 120 | 1102 | |||||
1102 | GYH | 330 | 1105 | |||||
1104 | FGH | 130 | 1104 | |||||
Requirement: | If Org_code and org_secondary_code are equal then report the sales against the Org_code in qlik table. | |||||||
we need to sync the Org_code and Org_secondary_code in set analysis expression. Which is not working. | ||||||||
Org_code is an dimension and we need to pass the values of Org_code foreach row in the set expression. | ||||||||
Expected ouput: | Org_code | Org_name | Sales | |||||
1106 | XYZ | 106+40 | ||||||
1102 | XCV | 0 | ||||||
1101 | HGF | 0 | ||||||
1104 | FGH | 130 | ||||||
Why don't you create a flag in the script for this
If(Org_code = Org_secondary_Code, 1, 0) as Flag
and then use this in your expression
Sum({<Flag = {1}>}Sales)
Set analysis is calculated once per chart (or table). NOT once per row so it is not the appropriate solution in your case.
You could use an If in script as Sunny suggests or in your table as Sales dimension:
If(Org_code = Org_secondary_Code, Sales, 0)
Not sure if having different names for Org_code 1106 in your example is intentional; if so you'd get unique lines for 1106 XYZ and 1106 UTY
Thanks!. It worked well with the flag. I made some changes to script as secondary Org code was coming from another table. so I merged the secondary code to the table where primary org code table with the below script.
Load*, If(primaryOrg_code=secondary_org_code,1,0) as flag
resident FACT_Primary;
drop table FACT_primary;
In the chart expression i just used the flag={1} in the set analysis expression Then everything worked well.