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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.