Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

How to group to get the last value and use it as a dimension

We are analyzing messaging traffic.

Each operation (use case) has some messages. For each operation, I'd like to get the last one MESSAGE_TYPE, but with a few exceptions (messages with errors or without importance)

OPERATION_IDMESSAGE_IDDATETIMEMESSAGE_TYPEHAS_ERRORS
112018-09-13 9:00CASE_USE_BEGININGFalse
122018-09-13 9:01STEP_1False
132018-09-13 9:02STEP_2False
142018-09-13 9:03STEP_3False
152018-09-13 9:04STEP_4False
162018-09-13 9:05CASE_USE_ENDFalse
172018-09-13 9:06NOT_IMPORTANTFalse
182018-09-13 9:07NOT_IMPORTANTFalse
292018-09-13 9:05CASE_USE_BEGININGFalse
2102018-09-13 9:06STEP_False
2112018-09-13 9:07STEP_2False

So, at 9:07, operation 1 has been finished (CASE_USE_END), and operation 2 has progressed until STEP_2


I got it working in a table in this way:

// LAST_USEFULL_MESSAGE_TYPE
// =========================
=FirstSortedValue(MESSAGE_TYPE,-aggr(Max(
if(HAS_ERRORS='True',0,
    if(MESSAGE_TYPE='NOT_IMPORTANT',0,
     [MESSAGE_DATE]
    ))
),OPERATION_ID, MESSAGE_TYPE))

I'd like to do a chart bar with LAST_USEFULL_MESSAGE_TYPE and Count(OPERATION_ID).

In order it to show the amount of operations (use cases) in ther current progress status.

But, QlikSense doesn't allow me to use a chart bar with two measures.  It requieres a dimension and a measure.

Then, I'd like to calculate the LAST_USEFULL_MESSAGE_TYPE in load script.  And use it as a measure.

I tryed the next:


[MESSAGES]:
load
  ... 
;
select
   OPERATION_ID, MESSAGE_ID, DATETIME, MESSAGE_TYPE, HAS_ERRORS
  
from messages
;

join
LOAD
     OPERATION_ID,
    
     FirstSortedValue(MESSAGE_TYPE,-Max(
       if(HAS_ERRORS=True,0,
       if(MESSAGE_TYPE='NOT_IMPORTANT',0,
           [MESSAGE_DATE]
       ))
  )) as LAST_USEFULL_MESSAGE_TYPE

  Resident [MESSAGES]
  Group by OPERATION_ID;

But it throws an error due to the FirstSortedValue/Max: "Nested aggregation not allowed"

Now, I'm thinking in creating an additional column to mark/rank the LAST_USEFULL_MESSAGE in order to pick the LAST_USEFULL_MESSAGE_TYPE.
But perhaps it's too complicated and there is an easier way to have the bar chart working.

How could I show the amount of operations (use cases) in ther current progress status?

Thanks in advance.

Best regards,

Virilo

0 Replies