Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Reply
boorgura
Specialist
Specialist

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.