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

[Qlik_Enthu]

Hi All,

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

ID	EVENT 1	EVENT 2	EVENT 3
ID1	ccc	Not received	Not received
ID1	Not received	cccc	Not received
ID1	Not received	Not received	cccc
ID2	xxx	Not received	Not received
ID2	Not received	xxx	Not received
ID2	Not received	Not received	xxx

But I need this to be as follow:

ID	EVENT 1	EVENT 2	EVENT 3
ID1	ccc	cccc	ccccc
ID2	xxx	xxxx	xxxxxx

Could someone help me how to achieve this? 

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

[Anil_Babu_Samineni]

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

[Qlik_Enthu]

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]

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]

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]

@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.

ID	event 1	event 2	event 3	event 4	event 5	event 6	event 7
1	Timestamp	-	Timestamp	-		Timestamp
2	-	Timestamp	-	Timestamp	Timestamp	-	Timestamp
3	Timestamp	-	-	-	-	-	-
4	-		Timestamp	-	-	-	-
2	Timestamp	-	-	-	-	Timestamp	-
3	-	Timestamp	-	-	-	-	Timestamp
1	-	-	-	Timestamp	-	-	-
4	-	-	-	-	-	-	-

Desired result: 

ID	event 1	event 2	event 3	event 4	event 5	event 6	event 7
1	Timestamp	Not Received	Timestamp	Timestamp	Not Received	Timestamp	Not Received
2	Timestamp	Timestamp	Not Received	Timestamp	Timestamp	Not Received	Timestamp
3	Timestamp	Timestamp	Not Received	Not Received	Not Received	Not Received	Timestamp
4	Not Received	Not Received	Timestamp	Not Received	Not Received	Not Received	Not 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]

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:

Using a expression similar to this:

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