How to control value replication in unpivot operation

skyline01 — Tue, 20 Mar 2018 19:49:44 GMT

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?

Re: How to control value replication in unpivot operation

swuehl — Tue, 20 Mar 2018 21:05:46 GMT

Maybe just create two tables linked by primary key, one that holds in addition Dim1Key and Measure1, and the other table Dim2 and Measure2.

Re: How to control value replication in unpivot operation

skyline01 — Tue, 20 Mar 2018 21:09:37 GMT

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.

topic Re: How to control value replication in unpivot operation in App Development

How to control value replication in unpivot operation

Re: How to control value replication in unpivot operation

Re: How to control value replication in unpivot operation