Need help in Incremnetal load

[mshailaja]

Hello all, Please answer my query below and give me the solution..

question: : we will get the latest file everyday from thespecific location and we willconcatenate with the existing QVD. Nowt,The challenge is for Monday we get 3 excel files  (as saturday and Sunday are holidays) and the script loads only the latest file and skips 2 files.

// To find the Latest file and time in a folder

for each File in filelist ('..\Desktop\testsample\sample*.*')

Folder:
Load '$(File)' as FileName,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File


LatestFile:
first 1
Load
FileName,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;


Let vLastReload=Peek('FileTime',0,'LatestFile');
LET vFileNamenew = subfield(File,'\',SubStringCount(File,'\')+1);



//After getting  find the latest file name concatenate with existing QVD....

Sample1:

LOAD S_no,
     Name,
     [Roll no],
     DOJ,
     marks
FROM
testsample\sample1.qvd
(qvd);

FOR EACH File in ('..\Desktop\testsample\sample*.xlsx')

//Exit Script;

Concatenate(Sample1)

LOAD S_no,
     Name,
     [Roll no],
     DOJ,
     marks

  FROM [testsample\$(vFileNamenew)] (ooxml, embedded labels, table is Branch);

NEXT

Store Sample1 into [testsample\sample1.qvd].(qvd);


Exit Script;

[asinha1991]

I dont understand certain part of your script like using for each second time but you can something on similar lines. Basically based on day of load you can decide if you want one latest or 3 latest. Look for the bold part and correct syntax if needed

for each File in filelist ('..\Desktop\testsample\sample*.*')

Folder:
Load '$(File)' as FileName,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File

if weekday(now())='Mon' then
LatestFile:
first 3
Load
FileName,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;
Let vLastReload=Peek('FileTime',0,'LatestFile');
LET vFileNamenew = subfield(Peek('FileName',0,'LatestFile'),'\',SubStringCount(File,'\')+1);
LET vFileNamenew1 = subfield(Peek('FileName',1,'LatestFile'),'\',SubStringCount(File,'\')+1);

LET vFileNamenew2 = subfield(Peek('FileName',2,'LatestFile'),'\',SubStringCount(File,'\')+1);

Delta:

LOAD S_no,
Name,
[Roll no],
DOJ,
marks

FROM [testsample\$(vFileNamenew)] (ooxml, embedded labels, table is Branch);
Concatenate

LOAD S_no,
Name,
[Roll no],
DOJ,
marks

FROM [testsample\$(vFileNamenew1)] (ooxml, embedded labels, table is Branch);

Concatenate

LOAD S_no,
Name,
[Roll no],
DOJ,
marks

FROM [testsample\$(vFileNamenew2)] (ooxml, embedded labels, table is Branch);

else
LatestFile:
first 1
Load
FileName,
FileTime,
1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;
Let vLastReload=Peek('FileTime',0,'LatestFile');
LET vFileNamenew = subfield(Peek('FileName',0,'LatestFile'),'\',SubStringCount(File,'\')+1);

Delta:

LOAD S_no,
Name,
[Roll no],
DOJ,
marks

FROM [testsample\$(vFileNamenew)] (ooxml, embedded labels, table is Branch);
end if

//After getting find the latest file name concatenate with existing QVD....

Concatenate(Delta)

LOAD S_no,
Name,
[Roll no],
DOJ,
marks
FROM
testsample\sample1.qvd
(qvd);

//Exit Script;

Store Delta into [testsample\sample1.qvd].(qvd);

Exit Script;