I am working with a QVD that looks like this:
FROM [lib://Transform QVD Path (osumc_helpstar)/ARPB_TX_VOID.qvd]
This is what I am trying to write using the QVD:
,CASE WHEN tdl.DETAIL_TYPE in (1) and (void.OLD_ETR_ID IS NULL AND void.REPOSTED_ETR_ID IS NULL)
cast(tdl.[ORIG_POST_DATE] - tdl.[ORIG_SERVICE_DATE] as integer)
END as ORIGINAL_LAG_SERVICE_TO_POST
**DETAIL TYPE AND THE DATES are from a different QVD that I would be Joining to
You can use if statement & resident load to get the data from the second QVD.
tdl.DETAIL_TYPE =1 and ISNUll(void.OLD_ETR_ID) AND IsNull(void.REPOSTED_ETR_ID),
INTERVAL(tdl.[ORIG_POST_DATE] - tdl.[ORIG_SERVICE_DATE] ,'dd') ) as ORIGINAL_LAG_SERVICE_TO_POST
I have not used a Resident load in this type of use case. How would it look if I am trying to get the "TDL" fields from this QVD?:
FROM [lib://Transform QVD Path (osumc_helpstar)/FactTable.qvd]
The general logic will be:
load Key(s), FurtherFields from QVD;
load Key(s), ExtraFields from OtherSource;
load *, CreateNewField resident t1;
drop tables t1;
whereby you need to use an if-loop with your conditions instead of a case-statement.
Alternative would be to load your ExtraFields at first within a mapping table and than using applymap() to access these values within your qvd-load: Mapping as an Alternative to Joining.
Thank you for the explanation. I have not used a Resident table so I am rather confused. Are you willing to put my use case in the appropriate format for my load script? I would really appreciate it.