Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rileymd88
New Contributor II

Check if QVD Exists Syntax Problem

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

1 Solution

Accepted Solutions

Re: Check if QVD Exists Syntax Problem

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

2 Replies

Re: Check if QVD Exists Syntax Problem

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

rileymd88
New Contributor II

Re: Check if QVD Exists Syntax Problem

Thank you this worked perfectly.

Community Browser