Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
hkg_qlik
Contributor III

Merging Row Data with same ID

Source Data:

IDDateTypeUnit 1Unit 2Unit 3
102018-04-30A12 19
102018-04-30B111520
102018-04-30C  202
122018-04-30A24 39
122018-04-30B221328
122018-04-30C  408
122018-03-31A48 25
122018-03-31B441442
122018-03-31C  608

 

 

Output required:

IDDateTypeUnit 1Unit 2Unit 3
102018-04-30A1215202
122018-04-30A2413408
122018-03-31A4814608

 

Can we do it on the front end without any changes to the back-end script/Data Model?

1 Solution

Accepted Solutions
lblumenfeld
Contributor

Re: Merging Row Data with same ID

No need to create a separate table in the load script.

On your sheet, make a table with

Dimension = ID
Dimension = Date        
Measure = FirstValue(Type)
Measure = Max({<[Type]={"A"} >} Unit1)
Measure = Max({<[Type]={"B"} >} Unit2)
Measure = Max({<[Type]={"C"} >} Unit3)

The dimension = Date assumes you want on of these for each date within ID, or you only have one date per ID. Otherwise, you can use the measure from my prior reply.

4 Replies
lblumenfeld
Contributor

Re: Merging Row Data with same ID

When you say front-end, do you mean in a table?

IT looks like you want the ID, maximum date, first value of type and the max of Unit1, Unit2, Unit3. IS this correct? If so then

Make a table with

Dimension = ID
Measure = Max(Date)
Measure = FirstValue(Type)
Measure = Max(Unit1)
Measure = Max(Unit2)
Measure = Max(Unit3)

Channa
Valued Contributor III

Re: Merging Row Data with same ID

Simply do sort on Type

Channa
hkg_qlik
Contributor III

Re: Merging Row Data with same ID

Thanks for the response, let me clarify the requirement further.

I am having this data in Qlik Sense Table object:

IDDateTypeUnit 1Unit 2Unit 3
102018-04-30A12 300
102018-04-30B201520
102018-04-30C  202

 

I would like to convert the above table into another Qlik Sense Table:

Need to convert the 3 rows into one where Type = A but values corresponding of Unit 1 for A, Unit 2 for Type = B and Unit 3 Value for Type = C. Its should all show as below under Type A record.

FYI: The color code will shows the mapping required to convert into single row.

IDDateTypeUnit 1Unit 2Unit 3
102018-04-30A1215202
lblumenfeld
Contributor

Re: Merging Row Data with same ID

No need to create a separate table in the load script.

On your sheet, make a table with

Dimension = ID
Dimension = Date        
Measure = FirstValue(Type)
Measure = Max({<[Type]={"A"} >} Unit1)
Measure = Max({<[Type]={"B"} >} Unit2)
Measure = Max({<[Type]={"C"} >} Unit3)

The dimension = Date assumes you want on of these for each date within ID, or you only have one date per ID. Otherwise, you can use the measure from my prior reply.