adapt Group By from script to measure

Cobran53 — Tue, 18 Jun 2024 11:56:51 GMT

Hey ! I have a table like this:
Ticket Number, Item Number, Task Number, Ticket_Status, Item_Status, Task_Status
1, 1, 1, Closed, Closed, Closed
1, 1, 2, Closed, Closed, Closed
1, 2, 1, Closed, Open, Closed
2, -, -, Closed, -, -
3, 1, 1, Closed, Closed, Open
3, 1, 2, Closed, Closed, Closed
4, 1, 1, Closed, Closed, Closed
4, 1, 2, Closed, Closed, Closed
4, 2, 1, Closed, Closed, Closed

My goal is to detect inconsistencies, where everything isn't closed correctly. In Qlik Script, I've done something like this :
1) Load my ticket table as normal
2) Make a table which loads in the resident table Tickets, groups by Ticket_Number, and counts the number of total items, of total tasks, of closed tasks, and of closed items.
3) Left Join my table tickets with a table with my ticket_number and a mismatch value, calculated with the calculations from (2).

This works, but this slows down a bit the loading of the table, and my superior would rather I do a measure in the sheet directly.

The thing is, I haven't been able to obtain the same results. I'm not able to count all total items and all closed items for a single ticket. Using Total doesn't get everything, and using ALL or {1} makes it so the table cannot be filtered anymore. I've tried many aggregations without success. The results have never been exact anyway.

Here's how I compute each of those variables (with total here, but i tried everything I mentionned,) :
* Total Item Count : Count(Total Item_Number)
* Total Task Count : Count(Total Task_Number)
* Closed Item Count : Sum(Total If(WildMatch(Item_Status, 'Cancel*', 'Close*', 'Resolved'), 1, 0))
* Closed Task Count : Sum(Total If(WildMatch(Task_Status, 'Cancel*', 'Close*', 'Resolved'), 1, 0))

What am I doing wrong?

Re: adapt Group By from script to measure

marcus_sommer — Tue, 18 Jun 2024 13:21:40 GMT

I assume that many of your UI attempts with aggr() and/or TOTAL and/or {1} didn't address the underlying cause and making therefore all only complicated because NULL isn't stored in any way and couldn't be directly accessed/selected.

There are indirect ways but I wouldn't recommend them - they should be rather considered as a worst case scenario. Often better is to replace NULL during the load - this might be done with simple measurements like:

if(isnull(MyField), '<NULL', MyField) as MyField

if(len(trim(MyField)), MyField, '<NULL') as MyField

An alternatively to the above functions are coalesce() or alt() or by more fields the use of the various NULL variables.

Beside of this I would remain to do the essential things within the script - probably by flagging the records and without touching the NULL, maybe with something:

sign(len(F1)) * sign(len(F2)) * sign(len(F3)) as [0/1 Flag],
rangesum(sign(len(F1)), sign(len(F2)), sign(len(F3))) as [Quality Flag]

Such measurements are fast within the load - not like aggregation-loads - and quite simple sum() + count() with/without set analysis and/or as dimensions/selections should enable all needed views.

topic adapt Group By from script to measure in App Development

adapt Group By from script to measure

Re: adapt Group By from script to measure