Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Need help in Incremnetal load

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;

 

1 Reply
Highlighted
Creator III
Creator III

Re: Need help in Incremnetal load

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;