4 Replies Latest reply: Aug 24, 2017 6:11 AM by Mike Seisbye RSS

    Concenate & Preceding load

    Mike Seisbye

      So i have 2 files. from where i'm getting my sales info.

       

      The QVD file are the historical Data. here's all the closed months data.  The xslx file are then containing open orders and the last 90 days sales.

       

      So What i'm trying to achieve are to load the qvd file.  and then concenate with all the lines, which aren't allready loaded.  Since the xlsx file contains 90 days of sales. The 2 files will overlap.

       

      So how to achieve this?  My first approach, were to do a peek on maximum invoice date in the qvd file, and then concenate records higher than max date.  Problem here are  open orders don't have invoice dates. (without going into details, order date can't be used). 

       

      Therefor i needed another approach, if i'm to avoid loading order details to another table(which i would prefer).

       

      The data comes without the ID.  So i just want to create the ID in the load, on the variable that creates a unique ID.  then do a Where not exists ID. which is what i'm trying below.

       

      The problem are that since Unique ID aren't created before the load.  Hence i need a preceding load.  But this is not working to well for me?   I'm thinking maybe i need to load the xlsx to a temp. table.  then load the qvd and concenate with the temp. table.  and then drop the temp table?  Am i correct?  Will this be the best approach. if yes.  have is this best done? (could some on show the syntax)

       

      Thanks.

       

      Code so Far

       

      ____________________________

       

      Sales:

       

      LOAD InvNumber,

      InvoiceDate,

      'Invoiced' as OrderStatus,

           Company,

           OrderNumber,

           OrderType,

           OrderLineNumber,

           OrderDate,

      (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

          

      FROM

      [Sales.qvd] (qvd);

       

      [Current Sales]:

       

       

      Concatenate(Sales)

       

       

      LOAD *,

      InvNumber,

      If(Len(InvNumber)=0, 'Open', 'Invoiced') as OrderStatus,

      InvoiceDate,

      InvMonth,

           InvYear,

           Company,

           OrderNumber,

           OrderType,

           OrderLineNumber,

           OrderDate;

      Load     

           (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID 

         

      FROM

      [Orders_Ext std3-9-20 NR.xlsx]

      (ooxml, embedded labels, header is 1 lines, table is Sheet1) Where Not Exists(OrderLineID);

        • Re: Concenate & Preceding load
          Tresesco B

          Not sure if understood right. Perhaps, you just have to create new fields (for second table) in one load statement and do-away with preceding load, like:

           

          Sales:

           

          LOAD InvNumber,

          InvoiceDate,

          'Invoiced' as OrderStatus,

               Company,

               OrderNumber,

               OrderType,

               OrderLineNumber,

               OrderDate,

          (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

            

          FROM

          [Sales.qvd] (qvd);

           

          [Current Sales]:

           

           

          Concatenate(Sales)

           

           

          LOAD *,

          InvNumber,

          If(Len(InvNumber)=0, 'Open', 'Invoiced') as OrderStatus,

          InvoiceDate,

          InvMonth,

               InvYear,

               Company,

               OrderNumber,

               OrderType,

               OrderLineNumber,

               OrderDate ,

               (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID

           

          FROM

          [Orders_Ext std3-9-20 NR.xlsx]

          (ooxml, embedded labels, header is 1 lines, table is Sheet1) Where Not Exists(OrderLineID, (Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber));.

           

          Edit: And yes, noticed the Not Exists later, and modified accordingly.