2 Replies Latest reply: Apr 21, 2016 4:49 AM by Riley MacDonald RSS

    Check if QVD Exists Syntax Problem

    Riley MacDonald

      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

          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