5 Replies Latest reply: Nov 15, 2017 7:43 PM by Luis Madriz RSS

    Finding most recent date in qlik sense

    Amuktha Chakilam

      Hi all,

       

      I have two tables with calls data. The first table has id, phone number, call time stamp(MM/DD/YYYY hr min sec); Second table has phone number and time stamp(this time stamp is different from the one in table 1) but id is missing.

       

      How to look up the phone number from table 2 in table 1 and find id based on the most recent record of that phone_number in table 1?

       

       

      Table1.JPG

      Table 2.JPG

      I appreciate any help with this.

       

      Thank you

        • Re: Finding most recent date in qlik sense
          Luis Madriz

          Hi,

           

          This is what I'm understanding:

          1. Table 2 doesn't have Id populated
          2. With the phone number of Table 2 you need to go into Table 1 and get the Id of the most recent call

           

          If that's the case, the second record in Table 2 (1234 + 6/7/2017) should be getting and Id of 1 instead of 2. And the calls for 8910 should both get either 4 or 5 whichever occurred first

           

          Please confirm,

           

          Cheers,

           

          Luis

            • Re: Finding most recent date in qlik sense
              Amuktha Chakilam

              Hi Luis,

               

              Thanks for the reply.

               

              Yes, id in the second table has to be generated based on the most recent occurrence of that number in table 1

               

              I had a typo in the date ; I fixed it. Thanks for letting me know.

               

              Basically, all these phone call records have to placed in a chronological order from both tables and id for a record in table 2 is the id of the most recent record of that phone number in table 1 before the time stamp of the record from table 2.

               

              final.png

              I hope this is clear. Thanks

            • Re: Finding most recent date in qlik sense
              Shraddha Gajare

              PFA.

              Is this what needed?

              • Re: Finding most recent date in qlik sense
                Luis Madriz

                Ok, this should do it. Please try it and keep me posted,

                Cheers,

                Luis

                 

                Table1:

                Load

                id,

                phone_number,

                call_date,

                call_hr,

                call_min,

                call_sec

                FROM [lib://AttachedFiles/Table1.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                // Mapping Table with only the latest call

                Temp:

                NoConcatenate

                Load

                phone_number,

                id,

                max(call_date) as max_call_date

                Resident Table1

                Group by phone_number,id;

                 

                MappingTable:

                Mapping Load

                phone_number,

                id

                Resident Temp;

                Drop table Temp;

                 

                // Concatenating the two table and finding the id for Table2

                Concatenate('Table1')

                Load *,

                     ApplyMap('MappingTable', phone_number,'N/A') as id;

                LOAD

                    phone_number,

                    call_date,

                    call_hr,

                    call_min,

                    call_sec

                FROM [lib://AttachedFiles/Table2.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                // Sorting

                BothTables:

                NoConcatenate

                Load *

                Resident Table1

                Order by id, phone_number, call_date;

                 

                Drop Table Table1;