Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
nishaloganathan
Contributor II

Latest Records in Qlik View Script

Hello All,

I have data something like below -

    

ConversationIDClosedDateRegionLangUID
129/3/2017EnglishL1
129/9/2017EnglishL1
139/15/2017DanishL2
149/17/2017PortugueseL3
1410/15/2017Portuguese

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 -

    

ConversationIDClosedDateRegionLangUID
129/9/2017EnglishL1
139/15/2017DanishL2
149/17/2017PortugueseL3
1410/15/2017PortugueseL3

Can anyone help me to achieve this?

Tags (1)
1 Solution

Accepted Solutions
stabben23
Honored Contributor

Re: Latest Records in Qlik View Script

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;

4 Replies
Chanty4u
Esteemed Contributor III

Re: Latest Records in Qlik View Script

use

firstsortedvalue(Id,-CloseDate)

nishaloganathan
Contributor II

Re: Latest Records in Qlik View Script

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.

stabben23
Honored Contributor

Re: Latest Records in Qlik View Script

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;

nishaloganathan
Contributor II

Re: Latest Records in Qlik View Script

Thank You.. Its working now..