Need assistance in scripting

[ashmitp869]

Hi ,

Date 15 file :

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)

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;