Finding most recent date in qlik sense

[achakilam1022]

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?

I appreciate any help with this.

Thank you

[luismadriz]

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

[shraddha_g]

PFA.

Is this what needed?

[achakilam1022]

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.

I hope this is clear. Thanks

[achakilam1022]

Thanks for the reply

But, that's not quite what I'm looking for.

[luismadriz]

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;

[luismadriz]

Hi,

Just wondering to know how did you go,

Cheers

Luis

When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

[achakilam1022]

Luis,

Your approach gave me a good idea how to solve this problem.

Thanks!

[luismadriz]

Good to hear,

When you can, please mark the appropriate replies as Correct or Helpful

Cheers,

Luis