1 Reply Latest reply: Jan 11, 2018 1:56 PM by Petter Skjolden RSS

    combining columns and removing duplicates

    SABINA Balan
    SABINA Balan Jan 11, 2018 5:15 AM

      Hi All,

      Please help with an advice as I am pretty new to qlik sense

      For each customers we have different promotions for different products.

      When loading the customer database, I want to obtain a new column called "Final Promo" based on the following rules:

      - I am keeping only the codes endind in 15, 16 or 17 with the priority in reverse (highest number will be kept)

      -Removing duplicates, if the same code apears more than once in the columns Promo 1 to Promo 4

       

      Thanks in advance for your support

       

      CustomerPromo1Promo2Promo3Promo4Final Promo
      1TLS_916ExtraComp150 TLS_916
      2 SOHO_SPR2011_INT -
      3AC100AC100AC100 -
      4TLS_15TLS_15TLS_15 TLS_15
      5 TLS417TLS417TLS417TLS417
      6TLS_15TLS417TLS417
      • 72 Views
      • Tags:
        • Re: combining columns and removing duplicates
          Petter Skjolden
          Petter Skjolden Jan 11, 2018 1:56 PM (in response to SABINA Balan)

          By transforming the source customer table of 6 rows by 5 columns into a 24 rows by 3 columns it will be easier to create the logic to pick the right promo for each customer:

           

          LOAD SCRIPT:

          2018-01-11 19_50_50-Qlik Sense Desktop.png

           

          Then using a Pivot Table and the expression

           

              SubField( Concat({<Code={"*15","*16*","*17"}>} Code,'|' , -Right(Code,2) ) ,'|' , 1 )

           

          for the Final Promo measure you can get this:

           

           

          2018-01-11 19_47_41-Qlik Sense Desktop.png

          I have attached a QVF Qlik Sense application that these screenshots are taken from...