Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Source Data:
ID | Date | Type | Unit 1 | Unit 2 | Unit 3 |
10 | 2018-04-30 | A | 12 | 19 | |
10 | 2018-04-30 | B | 11 | 15 | 20 |
10 | 2018-04-30 | C | 202 | ||
12 | 2018-04-30 | A | 24 | 39 | |
12 | 2018-04-30 | B | 22 | 13 | 28 |
12 | 2018-04-30 | C | 408 | ||
12 | 2018-03-31 | A | 48 | 25 | |
12 | 2018-03-31 | B | 44 | 14 | 42 |
12 | 2018-03-31 | C | 608 |
Output required:
ID | Date | Type | Unit 1 | Unit 2 | Unit 3 |
10 | 2018-04-30 | A | 12 | 15 | 202 |
12 | 2018-04-30 | A | 24 | 13 | 408 |
12 | 2018-03-31 | A | 48 | 14 | 608 |
Can we do it on the front end without any changes to the back-end script/Data Model?
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.
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)
Simply do sort on Type
Thanks for the response, let me clarify the requirement further.
I am having this data in Qlik Sense Table object:
ID | Date | Type | Unit 1 | Unit 2 | Unit 3 |
10 | 2018-04-30 | A | 12 | 300 | |
10 | 2018-04-30 | B | 20 | 15 | 20 |
10 | 2018-04-30 | C | 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.
ID | Date | Type | Unit 1 | Unit 2 | Unit 3 |
10 | 2018-04-30 | A | 12 | 15 | 202 |
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.