Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Enthu
Creator II
Creator II

Straight Table: Combining multiple records based on an ID into a single record

Hi All,

I am currently getting data in my straight table as follows:

IDEVENT 1EVENT 2EVENT 3
ID1cccNot receivedNot received
ID1Not receivedccccNot received
ID1Not receivedNot receivedcccc
ID2xxxNot receivedNot received
ID2Not receivedxxxNot received
ID2Not receivedNot receivedxxx

 

But I need this to be as follow:

IDEVENT 1EVENT 2EVENT 3
ID1cccccccccccc
ID2xxxxxxxxxxxxx

 

Could someone help me how to achieve this? 

P.s I am trying with Group By but not fruitful

6 Replies
Anil_Babu_Samineni

Why only CCC and XXX since you have the same for others also?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Qlik_Enthu
Creator II
Creator II
Author

the ccc xxx are timestamps..they may appear or may not for few events..all i wud wanted is  to consolidate the  IDs with multiple entries for different events into a single row wherever the event has data for timestamp it has to appear else the literal 'Not Received'

I am trying to achieve this with joins but could not get the desired results..

will be nice if i get a solution..

GaryGiles
Specialist
Specialist

For each event, use the following formula in a straight table measure for EVENT 1, EVENT 2 and EVENT 3:

=maxstring({$<[EVENT 1]-={'Not received'}>} [EVENT 1])

You might have to use max instead of maxstring if it is timestamp field.

Vegar
MVP
MVP

If only one of the three holds a value then you could try using RangeMax ().

=Timestamp(RangeMax(timestamp (EVENT1), timestamp (EVENT2) , timestamp (EVENT3))) 

Qlik_Enthu
Creator II
Creator II
Author

@GaryGiles hi there...thanks for your inputs.. But My requirement is to display all the  timestamps..not only the max..

My sample data would look like this below.

IDevent 1event 2 event 3 event 4 event 5 event 6event 7
1Timestamp-Timestamp- Timestamp 
2-Timestamp-TimestampTimestamp-Timestamp
3Timestamp------
4- Timestamp----
2Timestamp----Timestamp-
3-Timestamp----Timestamp
1---Timestamp---
4-------

 

Desired result: 

IDevent 1event 2 event 3 event 4 event 5 event 6event 7
1TimestampNot ReceivedTimestampTimestampNot ReceivedTimestampNot Received
2TimestampTimestampNot ReceivedTimestampTimestampNot ReceivedTimestamp
3TimestampTimestampNot ReceivedNot ReceivedNot ReceivedNot ReceivedTimestamp
4Not ReceivedNot ReceivedTimestampNot ReceivedNot ReceivedNot ReceivedNot Received

 

Currently I am trying to use Apply Map ( on the same table taking only EVENT TYPE and TIMESTAMP Fields), Concatenating the tables in QVD based on EVENT_TYPES and Joining the same tables in the presentation layer to consolidate the rows.

However, achieved only partial success. The IDs are consolidated, but the timestamp values are not picked up nor the condition to display 'Not Received'

Vegar
MVP
MVP

Take a look at my attached solution and sample data. 

Using this sample: 

Table:
LOAD * inline [
ID,event 1,event 2,event 3,event 4,event 5,event 6,event 7
1,2020-11-09 09:30:00,-,2020-11-09 09:40:00,-,,2020-11-09 13:40:00
2,-,2020-11-09 01:40:00,-,2020-11-10 09:20:00,2020-11-10 11:00:00,-,2020-11-10 11:22:00
3,2020-11-10 11:30:00,-,-,-,-,-,-
4,-,,2020-11-10 16:30:00,-,-,-,-
2,2020-11-11 06:30:00,-,-,-,-,2020-11-11 06:32:00,-
3,-,2020-11-11 14:12:00,-,-,-,-,2020-11-11 21:22:00
1,-,-,-,2020-11-11 14:22:00,-,-,-
4,-,-,-,-,-,-,-];

I create this straight table in Qlik Sense:

Vegar_0-1604909223837.png

Using a expression similar to this:

=alt (only(timestamp([event 1])), 'Not recieved')