Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working in Qlik Sense 3.2. I have a fact table that I need to unpivot. My challenge is that I already have some measure columns present in my fact table. If I simply unpivot the table (resulting in one more dimension column and one more measure column), the values in the other measure columns will be replicated. I do not want the values in these measure columns to be replicated. For example, suppose I have the following fact table:
PrimaryKey | Dim1Key | Measure1 | DimMeasure1 | DimMeasure2 |
---|---|---|---|---|
1 | 10 | 15 | 2 | 3 |
2 | 100 | 10 | 5 | 8 |
If I unpivot this table using the following code:
CrossTable(Dim2, Measure2, 3)
I will get the following resulting table:
PrimaryKey | Dim1Key | Measure1 | Dim2 | Measure2 |
---|---|---|---|---|
1 | 10 | 15 | DimMeasure1 | 2 |
1 | 10 | 15 | DimMeasure2 | 3 |
2 | 100 | 10 | DimMeasure1 | 5 |
2 | 100 | 10 | DimMeasure2 | 8 |
Instead, I want this resulting table to appear as the following:
PrimaryKey | Dim1Key | Measure1 | Dim2 | Measure2 |
---|---|---|---|---|
1 | 10 | 15 | DimMeasure1 | 2 |
1 | 10 | - | DimMeasure2 | 3 |
2 | 100 | 10 | DimMeasure1 | 5 |
2 | 100 | - | DimMeasure2 | 8 |
where '-' in Measure1 indicates a null. Is there a way to prevent the already-existing measure values (e.g., Measure1) from being replicated in the unpivot operation?
Maybe just create two tables linked by primary key, one that holds in addition Dim1Key and Measure1, and the other table Dim2 and Measure2.
Maybe just create two tables linked by primary key, one that holds in addition Dim1Key and Measure1, and the other table Dim2 and Measure2.
I was considering either doing that or assigning a sequence number to each group of PrimaryKey values and nulling out the measure values where sequence <> 1. I think your solution will be easier to implement, though.