2 Replies Latest reply: Feb 17, 2014 2:39 PM by neetha P RSS

    incremental load with temporary load and multiple files

      Hi,

       

      I receive multiple files (2 per month called YYYYMM.xlsx and YYYYMMx.xlsx) and I want

       

      1)      Determine the difference between the two files with same YYYYMM and add the difference to a QVD result called (Final.qvd) (DONE)

      2)      Load all the files and run the script to add each time the result to the final.result

      3)      Realize a incremental load based on this rule above when another month is added ?

       

       

      My script is temporally with an example 201202.xlsx and 201202x.xlsx but how perform the 2) and 3)?

       

      FileA :LOAD
          
      filename() as Filename,
          
      DATE(DATE#(left(right(filename(),11),6),'YYYYMM'),'MM/YYYY') as Filedate,
          
      Piece,
            
      MontantFact,
            
      DateRappr,
          
      year(DateRappr) as year_rappr,
          
      month(DateRappr) as month_rappr,
          
      day(DateRappr) as day_rappr,
          
      DateValeur,
          
      MR,
            
      Post,
            
      MontantPost,
            
      Piece & Post as IDFROM$(vsourcedata)201202.xlsx
      (
      ooxml, embedded labels, table is Feuil1);
      store FileA into FileA.qvd (QVD);


      FileB :LOAD
          
      filename() as FilenameB,
          
      DATE(DATE#(left(right(filename(),12),6),'YYYYMM'),'MM/YYYY') as FiledateB,
          
      Piece,
            
      MontantFact,
            
      DateRappr,
          
      year(DateRappr) as year_rappr,
          
      month(DateRappr) as month_rappr,
          
      day(DateRappr) as day_rappr,
          
      DateValeur,
          
      MR,
            
      Post,
            
      MontantPost,
            
      Piece & Post as IDXFROM$(vsourcedata)201202X.xlsx
      (
      ooxml, embedded labels, table is Feuil1);
      store FileB into FileB.qvd (QVD);

      Final:NOCONCATENATE LOAD *RESIDENT FileAWHERE Exists(IDX,ID);CONCATENATE (Final)LOAD IDX as ID, MR, MontantFact, Piece, MontantPost, Post, DateRappr, FilenameB as Filename, FiledateB as FiledateRESIDENT FileBWHERE not Exists(ID,IDX);DROP TABLES FileA, FileB;
      store Final into Final.qvd (QVD)

       

      Thanks a lot,

       

      Thierry