Qlik Community

App Development

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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))) 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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')

Plees ekskuse my Swenglish and or Norweglish spelling misstakes