I am facing an issue in incremental logic with composite key. for insert and update
Incremental scenario is we have files updated every week. The file has UserId,Date and RiskId. RiskId is primary key. So, suppose on 24th jan the File has a data for one userID as below.
On 24th Jan, User 123 has Risk count as 5 for Risk1.
On 31st Jan, User 123 has risk count as 2 for Risk1
On 7th Feb, same user with same RiskID(Risk1) is updated as 0.
This repeats for all months. Now there is a line chart, where Month is the dimension and measure is count({<Risk={'High'}>}Risk ID).
For Jan Month, the count should show as 2 for User 123 and Risk1. In the month of Feb, User 123 for Risk1 should have 0 value.
I have implemented below logic for incremental.
24th File:
Load UserId,
RiskId,
Date,
RiskID&Date as key
from a.xlsx;
Store 24th File into 24.qvd;
For 31st FIle
31st File:
Load UserId,
RiskID,
Date ,
RiskID&Date as key from b.xlsx;
Store 31st File into 31.qvd;
Incremental Logic is as below.
Full:
Load *,
key as keyflag
from 24.qvd(qvd);
Concatenate(Full)
Incremental:
Load * from 31.qvd(qvd) where not exists(keyflag,key);
Drop field keyflag;
Store Full into 24.qvd(qvd);//This is the full load qvd. Now this will have 24 and 31 data
But in the graph when i see , i should get 2 for User 123 for RIsk1 as per 31st value, but it is showing count as 5 which is 24th value. Please assist with this.
