2 Replies Latest reply: Mar 20, 2018 5:09 PM by Casey Marincin RSS

    How to control value replication in unpivot operation

    Casey Marincin

      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?