topic Join two tables by comparing dates in QlikView

Join two tables by comparing dates

Wed, 08 Aug 2012 10:42:17 GMT

Hi guys,

I have two unrelated tables which i would like to join. Basically the primary table contains 'Interest_Charged' to a client. It has the following fields: Client_ID, Interest_amt and date. The second table has similiar fields for 'Brokerage_Charged' to a client with the following fields: Client_ID, Brokerage_amt, date and trader_ID. Please note that the primary table doesnt have a trader_ID

The two tables have no correlation with the other but i would like to assign a relevent trader_ID based on the dates. Basically I would like to join a trader_ID (from the Second table into the primary) by comparing the dates field of the two tables. So for a data set where Interest_charged.Client_ID = Brokerage_Charged.Client_ID I would like to find the relevant trader_ID where the date value of the two tables are the closest or minimum.

Can any one help me?

Regards

Faisal

Re: Join two tables by comparing dates

Wed, 08 Aug 2012 13:31:34 GMT

Date field are the same? what if data from primary table will not exists in secondary

lets start with attached qvw...

Re: Join two tables by comparing dates

Thu, 09 Aug 2012 05:08:38 GMT

The date fields might not be the same. However Client_ID will be the same, so you will find the same client_ID in the brokerage table and interest table. Essentially i want to find the trader_ID in the brokerage table where Interest.Client_ID = Brokerage.Client_ID and Brokerage.date <= Interest.date but the row with the closest date should be joined .

I have edited your xlsx fileto decipt the scenario

Regards

Faisal