Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
waleeed_mahmood
Creator
Creator

Matching a Dimension in a Measure

Hello all,

I have a question i cant seem to figure out. I have a table like below:

I have PI column in a flat table dimension and i am trying to apply the following logic to get the desired output column in the table below:

Desired Output  Formula:

if(match(trim("PI"),'SN','NP','') AND NOT (isnull("F L1") OR trim("F L1")=''),
Avg(Aggr((sum(Future Sim L2 NP) -sum(C L2 NP))/sum(C L2 NP),[SAPM],"MPG")))

NOTE: There is more to this formula but if the first if statement doesnt work, rest isnt valid anyway.

Probelm:

My set analysis gets stuck at the Match(trim(PI),'SN',NP',' ') statement and i cant figure out why. If i remove the IF statement, the measure works fine. If i remove the Match statement, measure works fine. Why cant it find a match in that column when its its that in the text?

 

Please let me know if you need more information.

 

Thank you.

PIMPGSAPMF L1C L2 NPC L2 SPF L2 NPF L2 SPFuture Sim L2 NPFuture Sim L2 SPDESIRED OUTPUT
NPK46ES71950AA05180587 621 62110000005.79%
NPK46ES71950AA151,737.005,660.00 5,990.00 599010000005.83%
NPK46ES71950AA2514,698.0047,900.00 50,700.00 5070010000005.85%
NPK46ES71950AA3586,533.00282,000.00 299,000.00 29900010000006.03%
NPK46ES71950BD04110359 380 38010000005.85%
NPK46ES71950BD141,093.003,560.00 3,770.00 377010000005.90%
NPK46ES71950BD249,024.0029,400.00 31,100.00 3110010000005.78%
NPK46ES71950BD3435,615.00116,000.00 123,000.00 12300010000006.03%
NPK46ES71950BD447,200.0023,500.00 24,800.00 2480010000005.53%
NPK46ES71950BF0295310 328 32810000005.81%
NPK46ES71950BF128682,830.00 2,990.00 299010000005.65%
NPK46ES71950BF227,349.0024,000.00 25,400.00 2540010000005.83%
NPK46ES71950BF3242,078.00137,000.00 145,000.00 1450001000000

5.84%

1 Solution

Accepted Solutions
waleeed_mahmood
Creator
Creator
Author

Hello all,

 

Thank you for your time. The issue was really stupid. It turns out PI is a function in Qlik and since i named my column that it wasnt working. So i renamed it to something else and it started working fine. Thank you @chrismarlow  for your time and help.

 

Best regards,

Waleed Mahmood.

View solution in original post

10 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Have you actually pasted your expression, or is it actually (your post seems to have double quotes & below swapped to []'s);

if(match(trim([PI]),'SN','NP','') AND NOT (isnull([F L1]) OR trim([F L1])=''),
Avg(Aggr((sum([Future Sim L2 NP]) -sum([C L2 NP]))/sum([C L2 NP]),[SAPM],[MPG])))

And if it is that does that still not work?

Cheers,

Chris.

 

waleeed_mahmood
Creator
Creator
Author

Hi Chris,

I am not sure I understand your point. You can use [] and double quotes interchangeably in QS. I also pasted my expression and it resolves to OK. Just doesn’t work due to the first IF condition.

 

Thank you.

chrismarlow
Specialist II
Specialist II

Hi,

I didn't know about the "" [] interchangability, I am still learning.

However I do seem to get a result with the []'s ...

20210727_1.png

Cheers,

Chris.

waleeed_mahmood
Creator
Creator
Author

Hey Chris,

Thank you for your time. I’ll give it a shot and have them all in []’s and see if that works. Also a side note to myself, the reason my expression might not be working is that if your column has a space in its name, you must wrap it in brackets or double quotes. So I’ll check that as well and see if I can get a result.

Cheers!

Waleed

waleeed_mahmood
Creator
Creator
Author

Hello Chris, unfortunately i am still unable to get the results you show in the image. Due to sensitive data i am unable to share my app. could you please share your script so i can further analyze it?

 

Thank you.

chrismarlow
Specialist II
Specialist II

No worries, attached the qvf (hopefully the right one).

waleeed_mahmood
Creator
Creator
Author

Hello,

 

I have noticed another issue. If i do a simple measure checking : if(match(PI,'NP') ,1,0) it gives me 0. However, if i put the same expression in a dimension, it gives me 1. is there something wrong with my dataset?

 

Thanks.

@sunny_talwar 

 

waleeed_mahmood
Creator
Creator
Author

Attached is the PI column as a measure and a dimension. I have never seen this before and i am lost.

 

PI.PNG

waleeed_mahmood
Creator
Creator
Author

Hello all,

 

Thank you for your time. The issue was really stupid. It turns out PI is a function in Qlik and since i named my column that it wasnt working. So i renamed it to something else and it started working fine. Thank you @chrismarlow  for your time and help.

 

Best regards,

Waleed Mahmood.