I am trying to do an incremental load if a QVD file exists, however I keep getting with one table loading. My code works when no QVD file exists, however when a QVD does exist I get the error that the table deliveryPlanNew cannot be found.
What I would like to do in words is check if the QVD deliveryPlan exists and if it does load from that QVD into a table called deliveryPlan and then load from my source (Excel files) that latest file I have from a dicrecotry into the table deliveryPlanNew and store this into a QVD file and then concatenate deliveryPlanNew QVD into the deliveryPlan table and overwrite the deliveryPlan QVD. If the QVD deliveryPlan does not exist then simply load all source files into a table called deliveryPlan and then store this into a QVD file called deliveryPlan.
Here is the code:
Let vDeliveryPlan = IsNull(QvdCreateTime('\deliveryPlan.qvd'));
If $(vDeliveryPlan) = 0 Then
deliveryPlan:
LOAD
[Market],
[Country SKU],
[Delivery Plan Version],
[Calendar Day],
[DP Lookup],
[Delivery Plan]
FROM [\deliveryPlan.qvd](qvd);
deliveryPlanNew:
LOAD
ApplyMap('custCodeMap',[Customer Code],null()) as [Market],
[Customer Item ID] as [Country SKU],
Date(Date#([Version Name], 'YYYYMMDD'),'DD/MM/YYYY') + 8 as [Delivery Plan Version],
[PIT Date] as [Calendar Day],
ApplyMap('custCodeMap',[Customer Code],null())&[Customer Item ID]&[Version Name]&[PIT Date] as [DP Lookup],
[Planned Deliveries to Vodafone WH] as [Delivery Plan]
FROM
[\$(latestDpFile)]
(ooxml, embedded labels);
Store deliveryPlanNew into deliveryPlanNew.qvd;
DROP Table deliveryPlanNew;
Concatenate(deliveryPlan)
LOAD
[Market],
[Country SKU],
[Delivery Plan Version],
[Calendar Day],
[DP Lookup],
[Delivery Plan]
FROM
[\deliveryPlanNew.qvd](qvd)
Where Not Exists([DP Lookup]);
Store deliveryPlan into deliveryPlan.qvd;
ELSE
deliveryPlan:
LOAD
ApplyMap('custCodeMap',[Customer Code],null()) as [Market],
[Customer Item ID] as [Country SKU],
Date(Date#([Version Name], 'YYYYMMDD'),'DD/MM/YYYY') + 8 as [Delivery Plan Version],
[PIT Date] as [Calendar Day],
ApplyMap('custCodeMap',[Customer Code],null())&[Customer Item ID]&[Version Name]&[PIT Date] as [DP Lookup],
Because your "deliveryPlanNew" table has the same fields as the deliveryPlan, it's automatically concatenating the two. Just add the NOCONCATENATE prefix in order to keep them separate:
Let vDeliveryPlan = IsNull(QvdCreateTime('\deliveryPlan.qvd'));
If $(vDeliveryPlan) = 0 Then
deliveryPlan:
LOAD
[Market],
[Country SKU],
[Delivery Plan Version],
[Calendar Day],
[DP Lookup],
[Delivery Plan]
FROM [\deliveryPlan.qvd](qvd);
deliveryPlanNew:
NOCONCATENATE LOAD
ApplyMap('custCodeMap',[Customer Code],null()) as [Market],
[Customer Item ID] as [Country SKU],
Date(Date#([Version Name], 'YYYYMMDD'),'DD/MM/YYYY') + 8 as [Delivery Plan Version],
[PIT Date] as [Calendar Day],
ApplyMap('custCodeMap',[Customer Code],null())&[Customer Item ID]&[Version Name]&[PIT Date] as [DP Lookup],
[Planned Deliveries to Vodafone WH] as [Delivery Plan]
FROM
[\$(latestDpFile)]
(ooxml, embedded labels);
Store deliveryPlanNew into deliveryPlanNew.qvd;
DROP Table deliveryPlanNew;
Concatenate(deliveryPlan)
LOAD
[Market],
[Country SKU],
[Delivery Plan Version],
[Calendar Day],
[DP Lookup],
[Delivery Plan]
FROM
[\deliveryPlanNew.qvd](qvd)
Where Not Exists([DP Lookup]);
Store deliveryPlan into deliveryPlan.qvd;
ELSE
deliveryPlan:
LOAD
ApplyMap('custCodeMap',[Customer Code],null()) as [Market],
[Customer Item ID] as [Country SKU],
Date(Date#([Version Name], 'YYYYMMDD'),'DD/MM/YYYY') + 8 as [Delivery Plan Version],
[PIT Date] as [Calendar Day],
ApplyMap('custCodeMap',[Customer Code],null())&[Customer Item ID]&[Version Name]&[PIT Date] as [DP Lookup],
Because your "deliveryPlanNew" table has the same fields as the deliveryPlan, it's automatically concatenating the two. Just add the NOCONCATENATE prefix in order to keep them separate:
Let vDeliveryPlan = IsNull(QvdCreateTime('\deliveryPlan.qvd'));
If $(vDeliveryPlan) = 0 Then
deliveryPlan:
LOAD
[Market],
[Country SKU],
[Delivery Plan Version],
[Calendar Day],
[DP Lookup],
[Delivery Plan]
FROM [\deliveryPlan.qvd](qvd);
deliveryPlanNew:
NOCONCATENATE LOAD
ApplyMap('custCodeMap',[Customer Code],null()) as [Market],
[Customer Item ID] as [Country SKU],
Date(Date#([Version Name], 'YYYYMMDD'),'DD/MM/YYYY') + 8 as [Delivery Plan Version],
[PIT Date] as [Calendar Day],
ApplyMap('custCodeMap',[Customer Code],null())&[Customer Item ID]&[Version Name]&[PIT Date] as [DP Lookup],
[Planned Deliveries to Vodafone WH] as [Delivery Plan]
FROM
[\$(latestDpFile)]
(ooxml, embedded labels);
Store deliveryPlanNew into deliveryPlanNew.qvd;
DROP Table deliveryPlanNew;
Concatenate(deliveryPlan)
LOAD
[Market],
[Country SKU],
[Delivery Plan Version],
[Calendar Day],
[DP Lookup],
[Delivery Plan]
FROM
[\deliveryPlanNew.qvd](qvd)
Where Not Exists([DP Lookup]);
Store deliveryPlan into deliveryPlan.qvd;
ELSE
deliveryPlan:
LOAD
ApplyMap('custCodeMap',[Customer Code],null()) as [Market],
[Customer Item ID] as [Country SKU],
Date(Date#([Version Name], 'YYYYMMDD'),'DD/MM/YYYY') + 8 as [Delivery Plan Version],
[PIT Date] as [Calendar Day],
ApplyMap('custCodeMap',[Customer Code],null())&[Customer Item ID]&[Version Name]&[PIT Date] as [DP Lookup],
