13 Replies Latest reply: Dec 28, 2015 3:02 AM by Ruslans KLimovs RSS

    Only last date record

    Ruslans KLimovs

      Hello!

       

      I have such SQL script:

       

      SELECT

       

        MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

        TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

        TABLE_Document_Rows."No_" AS ID

       

      FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

      WHERE

      TABLE_Document_Rows."FA Quantity" = 1

       

      GROUP BY

      TABLE_Document_Rows."No_",

      TABLE_Document_Rows."FA Location SubCode";

       

       

      And the result is:

      111.jpg

       

       

      I used MAX function to delete all duplicated rows with same ID. But it doesn't work. I need only one ID row with the lates date.

       

      How can I do it? I hope for your help.

        • Re: Only last date record
          Sunny Talwar

          May be add a preceding load like this:

           

           

          LOAD ID,

                    FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

                    Max(Post_Date) as Post_Date

          Group By ID;

          SELECT

            MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

            TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

            TABLE_Document_Rows."No_" AS ID

          FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

          WHERE

          TABLE_Document_Rows."FA Quantity" = 1

          GROUP BY

          TABLE_Document_Rows."No_",

          TABLE_Document_Rows."FA Location SubCode";

            • Re: Only last date record
              Ruslans KLimovs

              Uhh.. worst case. I works, but I don't understand till ens - how it works...

               

              Thank you anyway!! It will help me!




              Don't work. I got strange results - ID and Date are not correctly connected.

                • Re: Only last date record
                  Sunny Talwar

                  What did not work? Can you share the output of what you got when you ran the above code?

                    • Re: Only last date record
                      Ruslans KLimovs

                      Sure!

                       

                      The result:

                      111.jpg

                       

                       

                      And the real Date Base:

                      222.jpg

                       

                       

                      The latest date for STAND001604 in real base is 03.09.2015

                        • Re: Only last date record
                          Sunny Talwar

                          Seems like it may be an issue with your date interpretation:

                           

                          Can you check if you have this on the main page

                           

                          SET DateFormat='DD.MM.YY';

                           

                          and slight change in the script:

                           

                          SET DateFormat='DD.MM.YY';

                           

                          LOAD ID,

                                    FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

                                    Date(Max(Post_Date)) as Post_Date

                          Group By ID;

                          SELECT

                            MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

                            TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

                            TABLE_Document_Rows."No_" AS ID

                          FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

                          WHERE

                          TABLE_Document_Rows."FA Quantity" = 1

                          GROUP BY

                          TABLE_Document_Rows."No_",

                          TABLE_Document_Rows."FA Location SubCode";

                            • Re: Only last date record
                              Ruslans KLimovs

                              Yes, I have this format and I also did Date(Max...) before.

                                • Re: Only last date record
                                  sujith madhavan

                                  Small tweaking to sunny's solution

                                  Please try this

                                   

                                  LOAD ID,

                                            FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

                                            FirstSortedValue(Post_Date, -Post_Date) as Post_Date

                                  Group By ID;

                                  SELECT

                                    MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

                                    TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

                                    TABLE_Document_Rows."No_" AS ID

                                  FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

                                  WHERE

                                  TABLE_Document_Rows."FA Quantity" = 1

                                  GROUP BY

                                  TABLE_Document_Rows."No_",

                                  TABLE_Document_Rows."FA Location SubCode";

                                    • Re: Only last date record
                                      sujith madhavan

                                      Looking at the data seems like the period in date format could be causing the issue

                                       

                                      LOAD ID,

                                       

                                                FirstSortedValue(Doc_Location_Subcode, -date(REPLACE(Post_Date,'.','/'),'DD/MM/YYYY')) as Doc_Location_Subcode,

                                      MAX(date(REPLACE(Post_Date,'.','/'),'DD/MM/YYYY')) as Post_Date

                                      RESIDENT TRNDATE

                                      Group By ID;

                                      • Re: Only last date record
                                        Ruslans KLimovs

                                        Thank YOU VERY much!!

                                         

                                        This works!

                                        • Re: Only last date record
                                          Ruslans KLimovs

                                          Still got one strange trouble.

                                           

                                          My code now is:

                                          LOAD

                                            ID,

                                             FirstSortedValue(Doc_Location_Subcode, -Post_Date) as Doc_Location_Subcode,

                                                    Date(FirstSortedValue(Post_Date, -Post_Date)) as Post_Date

                                                       

                                          Group By ID;

                                          SELECT

                                           

                                            MAX(TABLE_Document_Rows."Posting date") AS Post_Date,

                                           

                                            TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

                                            TABLE_Document_Rows."No_" AS ID

                                           

                                           

                                          FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

                                           

                                           

                                          WHERE

                                          TABLE_Document_Rows."FA Quantity" = 1

                                          AND

                                          TABLE_Document_Rows.Status2 = 4

                                           

                                           

                                          GROUP BY

                                          TABLE_Document_Rows."No_",

                                          TABLE_Document_Rows."FA Location SubCode",

                                          TABLE_Document_Rows.Status2;

                                           

                                           

                                          Looks like it works ... but for some minor ID - it DOESN'T . For exmaple for ID values EXTRAA003681 I got result:

                                          1.jpg

                                          But there are records in real base:

                                          2.jpg

                                           

                                           

                                          Added later:

                                          I have duplicated records with the same date, but function FirstStoredValue get error in this case. How can I chose only last record in this case?

                                            • Re: Only last date record
                                              sujith madhavan

                                              get every record From SQL, like this

                                               

                                              SELECT

                                               

                                                TABLE_Document_Rows."Posting date" AS Post_Date,

                                               

                                                TABLE_Document_Rows."FA Location SubCode" AS Doc_Location_Subcode,

                                                TABLE_Document_Rows."No_" AS ID

                                               

                                               

                                              FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS TABLE_Document_Rows

                                               

                                               

                                              WHERE

                                              TABLE_Document_Rows."FA Quantity" = 1

                                              AND

                                              TABLE_Document_Rows.Status2 = 4