4 Replies Latest reply: Oct 27, 2017 9:32 AM by Nisha Loganathan RSS

    Latest Records in Qlik View Script

    Nisha Loganathan

      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?