topic Check if QVD Exists Syntax Problem in QlikView

Check if QVD Exists Syntax Problem

rileymd88 — Wed, 20 Apr 2016 15:51:41 GMT

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],

[Planned Deliveries to WH] as [Delivery Plan]

FROM

[\*.xlsx]

(ooxml, embedded labels);

STORE deliveryPlan into deliveryPlan.qvd;

End If

Re: Check if QVD Exists Syntax Problem

Nicole-Smith — Wed, 20 Apr 2016 16:22:10 GMT

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],

[Planned Deliveries to WH] as [Delivery Plan]

FROM

[\*.xlsx]

(ooxml, embedded labels);

STORE deliveryPlan into deliveryPlan.qvd;

End If

Re: Check if QVD Exists Syntax Problem

rileymd88 — Thu, 21 Apr 2016 08:49:24 GMT

Thank you this worked perfectly.