Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
use
firstsortedvalue(Id,-CloseDate)
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.
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;
Thank You.. Its working now..