Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nishaloganathan
Creator II
Creator 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?

1 Solution

Accepted Solutions
stabben23
Master
Master

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;

View solution in original post

4 Replies
Chanty4u
MVP
MVP

use

firstsortedvalue(Id,-CloseDate)

nishaloganathan
Creator II
Creator II
Author

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
Master
Master

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;

View solution in original post

nishaloganathan
Creator II
Creator II
Author

Thank You.. Its working now..