Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sculptorlv
Contributor II

Only last date record

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.

1 Solution

Accepted Solutions
sujith1011
Contributor

Re: Only last date record

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";

12 Replies
MVP
MVP

Re: Only last date record

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";

sculptorlv
Contributor II

Re: Only last date record

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.

MVP
MVP

Re: Only last date record

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

sculptorlv
Contributor II

Re: Only last date record

Sure!

The result:

111.jpg

And the real Date Base:

222.jpg

The latest date for STAND001604 in real base is 03.09.2015

MVP
MVP

Re: Only last date record

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";

sculptorlv
Contributor II

Re: Only last date record

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

sujith1011
Contributor

Re: Only last date record

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";

sujith1011
Contributor

Re: Only last date record

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;

sculptorlv
Contributor II

Re: Only last date record

Thank YOU VERY much!!

This works!