Comparing two data sets A and B and retrieving 6 month backdated data from dataset A

Wed, 06 Jul 2016 09:43:34 GMT

Good day,

I'm comparing two data sets Repossessions and Accounts.

I want to compare the Account numbers from Repossessions for the current month and look it up from the Accounts on the current month to 6 months back.

Meaning if the date 2016/07/06 on the Repossession data set and its account number matches the account number on the Accounts data set within the period of 2016/02/06 and 2016/07/06, then count those account numbers.

Below is what I tried doing, but it doesn't give the desired results:

Temp:
LOAD ACCOUNT_NUMBER

FROM
$(vAccounts)\ACCOUNTS_*.QVD(QVD)
where date(OPEN_DATE, 'YYYY/MM/DD')<=Today() and date(OPEN_DATE, 'YYYY/MM/DD')>=AddMonths(date(OPEN_DATE, 'YYYY/MM/DD'),-6);

Left Join (Temp)
[ML_Repo]:

LOAD
       BOND_ACCOUNT_NUMBER as ACCOUNT_NUMBER,
     RESUBMISSION_REASON,
     BALANCE,
     VALUATION_AMT,
     SALE_AMT

From
$(vReposessions)\REPOSSESSIONS_MAR2016.QVD(QVD);

Your assistance is highly appreciated.

Regards,

Mbini

Re: Comparing two data sets A and B and retrieving 6 month backdated data from dataset A

boorgura — Wed, 06 Jul 2016 13:00:25 GMT

Temp:
LOAD ACCOUNT_NUMBER

FROM
$(vAccounts)\ACCOUNTS_*.QVD(QVD)
where date(OPEN_DATE, 'YYYY/MM/DD')<=Today() and date(OPEN_DATE, 'YYYY/MM/DD')>=AddMonths(date(today(), 'YYYY/MM/DD'),-6);

Please try the above code.

topic Comparing two data sets A and B and retrieving 6 month backdated data from dataset A in QlikView

Comparing two data sets A and B and retrieving 6 month backdated data from dataset A

Re: Comparing two data sets A and B and retrieving 6 month backdated data from dataset A