5 Replies Latest reply: Sep 10, 2017 5:04 AM by Tom Hovens RSS

    Load if.....

    Tom Hovens

      Hi Qlik'ers

       

      i want to only load a row when it has the highest rownumber per date

      DateItemRowNumberAmount
      01-09-2017A310
      01-09-2017A215
      01-09-2017A15
      02-09-2017A212
      02-09-2017A110

       

      To

      DateItemRowNumberAmount
      01-09-2017A310
      02-09-2017A212
        • Re: Load if.....
          Massimo Grossi

          1.png

           

          Table:

          LOAD Date,

               Item,

               RowNumber,

               Amount

          FROM

          [https://community.qlik.com/thread/273990]

          (html, codepage is 1252, embedded labels, table is @1);

           

           

          RK:

          Right Keep (Table)

          LOAD Date,

          max(RowNumber) as RowNumber

          RESIDENT Table

          GROUP BY Date;

           

           

          DROP TABLE RK;

          • Re: Load if.....
            Vineeth Pujari

            or maybe with Exists(),should be faster than Join or Keep when working with large datasets

             

            RK:

            LOAD Item&'_'&Date&'_'&max(RowNumber) as MaxRowKEY

            FROM

            [https://community.qlik.com/thread/273990]

            (html, codepage is 1252, embedded labels, table is @1)

            GROUP BY Item,Date;

             

             

            Table:

            LOAD Date,

                 Item,

                 RowNumber,

                 Amount

            FROM

            [https://community.qlik.com/thread/273990]

            (html, codepage is 1252, embedded labels, table is @1)

            Where Exists(MaxRowKEY,Item&'_'&Date&'_'&RowNumber);

            • Re: Load if.....
              Anand Chouhan

              Try this with MAX() and Where Exists and Group By.

               

              MainTable:

              LOAD Date(Date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') AS Date,Item,RowNumber,Amount,Date&Item&RowNumber as Key Inline

              [

              Date,Item,RowNumber,Amount

              01-09-2017, A, 3, 10

              01-09-2017, A, 2, 15

              01-09-2017, A, 1, 5

              02-09-2017, A, 2, 12

              02-09-2017, A, 1, 10

              ];

               

              tMax:

              LOAD Date,Item, MAX(RowNumber) as MaxRow

              Resident MainTable

              Group By Date,Item;

               

              MaxRow:

              LOAD Date&Item&MaxRow as MaxKey Resident tMax;

              DROP Table tMax;

               

              NoConcatenate

              Final:

              LOAD * Resident MainTable Where Exists(MaxKey,Key);

              DROP Table MainTable;


              OP1.PNG

              • Re: Load if.....
                Andrew Walker

                Hi Tom,

                After loading your data in the script (I've called the data table Table1, change to the correct tablename) add these lines :

                Inner Join(Table1)

                LOAD Date, max(RowNumber) as RowNumber

                Resident Table1 Group by Date;

                 

                giving:

                Date Item RowNumber Amount
                01-09-2017A310
                02-09-2017A212

                 

                Cheers

                 

                Andrew

                • Re: Load if.....
                  Tom Hovens

                  Thank you all for helping me out!