Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
mshailaja
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
asinha1991
Creator III
Creator III

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;