incremental load with temporary load and multiple files

Mon, 17 Feb 2014 15:45:02 GMT

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

Re: incremental load with temporary load and multiple files

Mon, 17 Feb 2014 19:08:41 GMT

Hi thierry, you want create the Final.qvd incrementally on every month.

Re: incremental load with temporary load and multiple files

Anonymous — Mon, 17 Feb 2014 19:39:27 GMT

Hi thierry,

You can do incremental load monthly with records added or modified every month.

Please see attach document.

topic Re: incremental load with temporary load and multiple files in QlikView

incremental load with temporary load and multiple files

Re: incremental load with temporary load and multiple files

Re: incremental load with temporary load and multiple files