Matching a Dimension in a Measure

[waleeed_mahmood]

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.

PI	MPG	SAPM	F L1	C L2 NP	C L2 SP	F L2 NP	F L2 SP	Future Sim L2 NP	Future Sim L2 SP	DESIRED OUTPUT
NP	K4	6ES71950AA05	180	587		621		621	1000000	5.79%
NP	K4	6ES71950AA15	1,737.00	5,660.00		5,990.00		5990	1000000	5.83%
NP	K4	6ES71950AA25	14,698.00	47,900.00		50,700.00		50700	1000000	5.85%
NP	K4	6ES71950AA35	86,533.00	282,000.00		299,000.00		299000	1000000	6.03%
NP	K4	6ES71950BD04	110	359		380		380	1000000	5.85%
NP	K4	6ES71950BD14	1,093.00	3,560.00		3,770.00		3770	1000000	5.90%
NP	K4	6ES71950BD24	9,024.00	29,400.00		31,100.00		31100	1000000	5.78%
NP	K4	6ES71950BD34	35,615.00	116,000.00		123,000.00		123000	1000000	6.03%
NP	K4	6ES71950BD44	7,200.00	23,500.00		24,800.00		24800	1000000	5.53%
NP	K4	6ES71950BF02	95	310		328		328	1000000	5.81%
NP	K4	6ES71950BF12	868	2,830.00		2,990.00		2990	1000000	5.65%
NP	K4	6ES71950BF22	7,349.00	24,000.00		25,400.00		25400	1000000	5.83%
NP	K4	6ES71950BF32	42,078.00	137,000.00		145,000.00		145000	1000000	5.84%

[chrismarlow]

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]

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]

Hi,

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

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

Cheers,

Chris.

[waleeed_mahmood]

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]

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]

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

[waleeed_mahmood]

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]

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

 

[waleeed_mahmood · Accepted solution]

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.