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
