Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
My task is next:
I use 3 columns: "ID_Transportation", "ID_User", "Status".
I want to find only ID_User that match two Statuses (for example, Status_A and Status_B). So my task is very similar to this: https://community.qlik.com/t5/New-to-Qlik-Sense/Using-P-with-multiple-criteria/m-p/21057. But its more difficult: ID_User should match two statuses which correspond to single transportation! I marked with red exact situation I trying to find in mini table below:
ID_Transportation | ID_User | Status
1 1 Status_A
1 1 Status_С
1 1 Status_B
1 2 Status_B
2 2 Status_A
2 and so on...
How I trying to solve this problem:
I trying to count for every ID_Transportation those Users ID which correspond to required statuses (Status_A and Status_B). Then I want to sort table and exclude zeros. And i will have only cases when rule is broken.
I use next formula:
count ( aggr (
{$<[ID_User]=p({<[Status]={"Status_A"}>})*p({<[Status]={"Status_B"}>})>} distinct [ID_User]
, [ID_Transportation]
) )
What is my problem:
If I select single ID_Transportation my formula counts times when rule was broken correctly.
But when I select several transportation, formula returns overestimated value. I believe, this is because formula counts all ID_User ever matched both statuses, even if that ID_User doesn`t match them for this particular ID_Transportation.
So how should i modify formula to met my goal??
Will be very grateful for help! Thank you!
It is still important to me.
I am literally broke my mind with this task in last couple days. So if anyone could give me advice, I would be incredibly happy.
Create a new field in the script like this
ID_Transportation & '|' & ID_User as ID_Key
and then use this
Count({$<[ID_Key] = P({<[Status] = {"Status_A"}>}) * P({<[Status] = {"Status_B"}>})>} DISTINCT [ID_User])
Thank you for reply.
Probably creating a new field assumes using Data Loading Editor, but I never done it before and dont know how. Maybe you can send me link to some tutorial, so I learn faster? My data loaded from OLE DB, I converted it in SSMS.