6 Replies Latest reply: Oct 25, 2012 6:39 AM by Some Nath Roy RSS

    Load Script handling fields

      Hi,

       

      I have a table with Order, Position and Delivery Date.

       

      A single Order, Position can has several records because we have several delivery date. For Example

       

      Order     Position     Delivery Date

      900          1               01/01/2012

      900          1               05/05/2012

      900          1               09/08/2012

       

      I should have only one record with the last delivery Date - Solution 1

       

      Order     Position     Delivery Date

      900          1              09/08/2012

       

      or. another solution...( Solution 2 )

       

      Order     Position     Delivery Date     Calculated Last Delivery

      900          1               01/01/2012     09/08/2012

      900          1               05/05/2012     09/08/2012

      900          1               09/08/2012     09/08/2012

       

       

      How can I do it in Load Script ?

       

      Thanks a lot

        • Re: Load Script handling fields
          Michael Jordan

          Hi,

           

          See attached Example

           

          You can drop the Test table once you have got the desired output.

          I just kept it for your reference.

           

          Thanks.

           

          Amar

            • Re: Load Script handling fields

              Sorry, the file is empty

              • Re: Load Script handling fields

                Sorry I wrong :-( The file is correct.

                I try your solution

                • Re: Load Script handling fields

                  I have a little issue more.

                   

                  I have two table created by qlikview, because i have to convert the date

                   

                  I import Table1

                   

                  Order     Position     Delivery Date

                  900          1               20120101

                  900          1               20120505

                  900          1               20120809

                   

                  so i change the format

                   

                  Table1:

                   

                  SQL SELECT Order,

                  Position,

                  DeliveryDate

                  FROM Table_Orders;

                   

                  Load Order

                  Position,

                  date(timestamp(DeliveryDate,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')as DeliveryDate1 resident Table_Orders;

                   

                  So in this case I have two tables, Table1 and Table1-1 generated by QlikView.

                   

                  Table1-1 generated from QlikView

                   

                  Order     Position     Delivery Date1

                  900          1               01/01/2012

                  900          1               05/05/2012

                  900          1               09/08/2012

                   

                  I should use the DeliveryDate1 but qlikview doesn't recognize it inside the table Table1

                • Re: Load Script handling fields
                  Some Nath Roy

                  A:
                  LOAD * Inline
                  [Order,     Position,     Delivery Date

                  900,          1,               01/01/2012

                  900,          1,               05/05/2012

                  900,          1,               09/08/2012];

                   

                  //Solution 1:

                  B:

                  LOAD Order, Position, Max(Date(Date#([Delivery Date],'DD/MM/YYYY'),'DD/MM/YYYY')) as [Calculated Last Delivery] Resident A Group By Order, Position;

                   

                  //Solution 2:
                  B:

                   

                  NoConcatenate
                  LOAD * Resident A;
                  Left Join
                  LOAD Order, Position, Max(Date(Date#([Delivery Date],'DD/MM/YYYY'),'DD/MM/YYYY')) as [Calculated Last Delivery] Resident A Group By Order, Position;

                   

                   

                  DROP Table A;