topic Re: Straight Table: Combining multiple records based on an ID into a single record in App Development

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

Qlik_Enthu — Fri, 06 Nov 2020 09:23:30 GMT

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

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

Anil_Babu_Samineni — Fri, 06 Nov 2020 10:19:02 GMT

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

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

Qlik_Enthu — Sat, 07 Nov 2020 14:09:48 GMT

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

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

GaryGiles — Sun, 08 Nov 2020 11:52:30 GMT

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.

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

Vegar — Sun, 08 Nov 2020 16:39:15 GMT

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

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

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

Qlik_Enthu — Mon, 09 Nov 2020 04:52:05 GMT

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

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

Vegar — Mon, 09 Nov 2020 08:07:51 GMT

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')