Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ashmitp869
Creator II
Creator II

Need assistance in scripting

Hi ,

Date 15 file :

ashmitp869_0-1648687055445.png

 

After load to qvd in qlikview by the script  i am creating extra field count_active when rowno() = 1 

if(rowno()=1, 1,0) as count_active,
if(rowno()=2, 1,0) as count_total_isolation,
if(rowno()=3, 1,0) as count_total_community,

 

But when I am downloading multiple files at a time - it not working - (sample file attached)

 

ashmitp869_2-1648687266216.png

 

ashmitp869_1-1648687118011.png

 

Here is the script I used -

if QvdCreateTime('[lib://QVD COVID19/SitRep/NSLHD HWC Exposures/HCWFulldata.qvd] (qvd)') > 0 then

// qvd exists

trace exists;

tmp:

LOAD MaxString(FileName) as MaxFileName from [lib://QVD COVID19/SitRep/NSLHD HWC Exposures/HCWFulldata.qvd] (qvd);

vMaxFileName = Peek('MaxFileName', 0, 'tmp');

trace vMaxFileName=$(vMaxFileName);

DROP Table tmp;

// get max file

tmpfiletoload:

load distinct FileName() as filename, '$(vMaxFileName)' as MaxFileName

FROM [lib://QVD COVID19/SitRep/NSLHD HWC Exposures/*.xlsx] (ooxml, embedded labels, table is [LHD Summary]);

// get excel to load

filetoload:

NoConcatenate load * resident tmpfiletoload where left(filename,6) > left(MaxFileName,6);

drop table tmpfiletoload;


//load excel

FOR i=0 to NoOfRows('filetoload')-1

f=Peek('filename', $(i), 'filetoload');

trace f=$(f);

allexcel:

load FileName() as FileName,
Date(Date#(left(FileName(),6),'DDMMYY'),'D/M/YYYY') as _Date,
if(rowno()=1, 1,0) as count_active,
if(rowno()=2, 1,0) as count_total_isolation,
if(rowno()=3, 1,0) as count_total_community,
Nursing,
Medical,
"Allied Health" as AH,
"Scientific/Technical" as scientific,
"Admin/ Corporate" as admin_corporate,
"Other",
"Total"

FROM [$(f)] (ooxml, embedded labels, table is [LHD Summary]);

NEXT;

// concat qvd and store

allexcel:

load * from [lib://QVD COVID19/SitRep/NSLHD HWC Exposures/HCWFulldata.qvd] (qvd);

STORE * from allexcel into [lib://QVD COVID19/SitRep/NSLHD HWC Exposures/HCWFulldata.qvd] (qvd);

ELSE

// qvd not exists, 1st load

trace not exists;

allexcel:

load

FileName() as FileName,

Date(Date#(left(FileName(),6),'DDMMYY'),'D/M/YYYY') as _Date,
if(rowno()=1, 1,0) as count_active,
if(rowno()=2, 1,0) as count_total_isolation,
if(rowno()=3, 1,0) as count_total_community,
Nursing,
Medical,
"Allied Health" as AH,
"Scientific/Technical" as scientific,
"Admin/ Corporate" as admin_corporate,
"Other",
"Total"
FROM [lib://QVD COVID19/SitRep/NSLHD HWC Exposures/*.xlsx] (ooxml, embedded labels, table is [LHD Summary]);

STORE * from allexcel into [lib://QVD COVID19/SitRep/NSLHD HWC Exposures/HCWFulldata.qvd] (qvd);

ENDIF;

Labels (1)
0 Replies