Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
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.
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.
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.
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.
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
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.
No worries, attached the qvf (hopefully the right one).
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.
Attached is the PI column as a measure and a dimension. I have never seen this before and i am lost.
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.