Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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";
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";
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.
What did not work? Can you share the output of what you got when you ran the above code?
Sure!
The result:
And the real Date Base:
The latest date for STAND001604 in real base is 03.09.2015
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";
Yes, I have this format and I also did Date(Max...) before.
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";
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;
Thank YOU VERY much!!
This works!