topic Re: Latest Records in Qlik View Script in QlikView

Latest Records in Qlik View Script

Anonymous — Fri, 27 Oct 2017 12:26:57 GMT

Hello All,

I have data something like below -

ConversationID	ClosedDate	Region	LangUID
12	9/3/2017	English	L1
12	9/9/2017	English	L1
13	9/15/2017	Danish	L2
14	9/17/2017	Portuguese	L3
14	10/15/2017	Portuguese	L3

Now there are two entries for the conversation IDs 12 and 14. But for ID -12 in the same month there are two entries. For ID - 14 there are two entries in the different month.

If it is same month I want to show only latest record. For EX: ID - 12 I should show 9/9/2017 record row.

If it is different month I want to show both the records. For EX: ID - 14 I should show 9/17/2017 and 10/15/2017 record rows.

This is the script that I am trying to achieve this -

Input:

LOAD ConversationID,

ClosedDate,

year(ClosedDate) as Year,

Month(ClosedDate) as Month,

Region,

LangUID

FROM

[Test_Latest Record.xlsx]

(ooxml, embedded labels, table is Sheet1);

Inner Join

Final:

Load ConversationID, Max(ClosedDate)as ClosedDate Resident Input group By ConversationID;

But this is not giving me two records it IDs belongs to two different months. This needs to be done in scripting.

Final Output should be like this -

ConversationID	ClosedDate	Region	LangUID
12	9/9/2017	English	L1
13	9/15/2017	Danish	L2
14	9/17/2017	Portuguese	L3
14	10/15/2017	Portuguese	L3

Can anyone help me to achieve this?

Re: Latest Records in Qlik View Script

Chanty4u — Fri, 27 Oct 2017 12:32:37 GMT

use

firstsortedvalue(Id,-CloseDate)

Re: Latest Records in Qlik View Script

Anonymous — Fri, 27 Oct 2017 12:43:10 GMT

Hello Chanty,

Thanks for your reply..

It wont work in my scenario.

As per the example that I have provided above ,

If it is same month I want to show only latest record. For EX: ID - 12 I should show 9/9/2017 record row.

If it is different month I want to show both the records. For EX: ID - 14 I should show 9/17/2017 and 10/15/2017 record rows.

Re: Latest Records in Qlik View Script

stabben23 — Fri, 27 Oct 2017 12:52:47 GMT

Put Month inside the Load and use it in the gruop by if you want to have max on every month.

Load ConversationID, Max(ClosedDate)as ClosedDate, Month Resident Input group By ConversationID, Month;

Re: Latest Records in Qlik View Script

Anonymous — Fri, 27 Oct 2017 13:32:44 GMT

Thank You.. Its working now..