2 Replies Latest reply: Nov 24, 2016 4:26 AM by Magnus Buseth RSS

    Combination of unpivot and pivot

    Magnus Buseth

      Hi,

       

      I'm new to Qlik Sense and hope you can help on the way with my first data import.

       

      I have a very wide table, with one row per customer, and one column per variable and period. The data looks something like this:

       

      CUSTOMER_IDVARIABLE_1_LY_P1VARIABLE_1_LY_P2VARIABLE_1_LY_P3....
      124812941125232....

       

      For each variable, there's 24 columns, one for each month of this year (TY) and last year (LY). With ~30 variables, that's more than 700 columns. The table has ~200k rows.

       

      I want to transform the data to have on column per variable, and one row per period (so 24 rows per customer instead of one). The end result would be something like this:

       

      CUSTOMER_IDPERIODVARIABLE_1VARIABLE_2...
      12481294131.01.201512345...
      12481294131.02.201552499

       

      So from what I understand I would have to first do a unpivot (cross table), "split" the variable column to create a period column, and then do a pivot (generic) again to get the variables as columns.

       

      Is this a sensible approach? How should I set up my load script?

       

      Thank you!