1 Reply Latest reply: Jul 5, 2018 5:01 PM by Nicole Smith RSS

    Load Unique Value based on a specific Row and Column

    Mayron Colville

      I'm looking to upload unique Values in which share the same ID and the price were unchanged from 1 week to another.

       

      Example:

       

      Data:

       

       

      IDPriceGroupUpdatedWeek
      11b3423
      12b3422
      23b3323
      23b3322
      34b3623
      35b3622
      46b2123
      46b2122
      52b2823
      54b28

      22

       

       

      Expected:

       

      IDPriceGroupUpdatedWeek
      11b123
      12b222
      34b523
      35b622
      52b923
      54b1022

       

      For Qlik Sense

        • Re: Load Unique Value based on a specific Row and Column
          Nicole Smith

          Using your example data, load script like this:

           

          Data:
          LOAD * INLINE [
              ID, Price, Group, UpdatedWeek
              1, 1b, 34, 23
              1, 2b, 34, 22
              2, 3b, 33, 23
              2, 3b, 33, 22
              3, 4b, 36, 23
              3, 5b, 36, 22
              4, 6b, 21, 23
              4, 6b, 21, 22
              5, 2b, 28, 23
              5, 4b, 28, 22
          ];
          
          
          Temp:
          LOAD ID, COUNT(DISTINCT Price) AS CountPrice
          RESIDENT Data
          GROUP BY ID;
          
          
          INNER JOIN (Data)
          LOAD ID
          RESIDENT Temp
          WHERE CountPrice > 1;
          
          
          DROP TABLE Temp;
          

           

          Returns a final table that looks like this:

          ID Price Group UpdatedWeek
          11b3423
          12b3422
          34b3623
          35b3622
          52b2823
          54b2822