Get distinct case_ids in an activity table (PQL)

Hi Finn,

Yes you can.

I hope you have created an OLAP table to show the KPI..

If yes - click on the OLAP table and in the right swim lane , go little down - you will find an option to tick "Distinct Values" if you click , only distinct values will appear.

Alternatively - If the number of dimensions you are showing in the OLAP table contains unique combination. You will not be able to see the distinct values as far as the unique combination exists in the result set

Hope this helps!

Please mark my response as best response , if this is helpful!

Regards

Ayan

Hi Ayan, thanks for your reply. No, I have not created a seperate OLAP table. I have applied my KPI-formula directly to the Activity table. Is there no way of adding a PQL command that gets rid of the duplicate entries in the KPI itself? I hope I described it well enough :)

@1460069622 

Hi Finn,

It is still not clear how you are applying KPI on a table directly. However, Please try using DISTINCT keyword before the actual column ( case_id) that may resolve this issue.

Regards

Ayan

Hi @1461001156, can you please share the PQL snippet of your KPI? Are you using a load script filter?

@Eugene Em This is my KPI PQL formula. I'm "filtering" for certain criteria.

The result is then a list of CaseIDs, however containing several entries of the same ID...

CASE WHEN

KPI("A") = 1 OR

KPI("B") = 1 OR

KPI("C") = 1

THEN

"TableName"."CASE_ID"

END

@1460069622 I tried the DISTINCT command in front of "TableName"."CASE_ID" but that doesn't work unfortunately.

Hi Finn ,

Use line it is shown below:

(DISTINCT CASE WHEN

KPI("A") = 1 OR

KPI("B") = 1 OR

KPI("C") = 1

THEN

"TableName"."CASE_ID"

END )

OR

DISTINCT (

CASE WHEN

KPI("A") = 1 OR

KPI("B") = 1 OR

KPI("C") = 1

THEN

"TableName"."CASE_ID"

END

)

Please mark my answer as Best answer if this is helpful.

Regards

Ayan

HI @1460069622 , Thanks for your input! I was thinking of something like this as well. However, I can't save the changes as "DISTINCT" is still marked as red (error?)

Hey @1461001156.

To make this work the way you need it to, you have to change the level on the KPI criteria.

I presume KPI ("A"), KPI ("B"), etc in the PQL you shared has the activities table level, something like this:

CASE WHEN "O2C_ACTIVITIES"."ATTRIBUTE" = 'X' THEN "O2C_ACTIVITIES"."CASE_KEY" ELSE NULL END

If so, then you need to convert it to the case level (since the case key is unique, you will not get duplicates, as you are receiving them now), so it will look something like this within the KPI:

CASE WHEN

PU_COUNT(

"O2C_CASES"

,"O2C_ACTIVITIES"."CASE_KEY"

, "O2C_ACTIVITIES"."ATTRIBUTE" = 'X'

) > 0

THEN "O2C_CASES"

ELSE NULL

END

Once you convert all KPIs into the CASES level, your overall PQL expression should look like:

CASE WHEN

KPI("A") = 1 OR

KPI("B") = 1 OR

KPI("C") = 1

THEN

"O2C_CASES"."CASE_ID"

END

Remember, the calculation will always default to the lowest level, even if only one of the elements in the calculation is on that level (in the example of CASES vs ACTIVITIES, ACTIVITIES is the lower level), so make sure that ALL criteria in your KPI are on the same level.

Let me know if that works.

Thanks!