Qlik Community

Qlik Sense App Development

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

skyline01
New Contributor

How to control value replication in unpivot operation

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:

PrimaryKeyDim1KeyMeasure1DimMeasure1DimMeasure2
1101523
21001058

If I unpivot this table using the following code:

CrossTable(Dim2, Measure2, 3)

I will get the following resulting table:

PrimaryKeyDim1KeyMeasure1Dim2Measure2
11015DimMeasure12
11015DimMeasure23
210010DimMeasure15
210010DimMeasure28

Instead, I want this resulting table to appear as the following:

PrimaryKeyDim1KeyMeasure1Dim2Measure2
11015DimMeasure12
110-DimMeasure23
210010DimMeasure15
2100-DimMeasure28

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to control value replication in 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.

2 Replies
MVP
MVP

Re: How to control value replication in 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.

skyline01
New Contributor

Re: How to control value replication in unpivot operation

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.

Community Browser