topic Re: Scripting Help in QlikView

Scripting Help

arusanah — Mon, 28 Nov 2016 18:51:37 GMT

I have a user Login data coming every month in excel File . Since the #of Logins vary every month, the data gets spread out in multiple Sheets accordingly. Concatenate in load script doesn't work since sheet count varies every month. I tried using following code but it doesn't work -- it gets into endless loop . Can anybody help me figure out missing piece of code

set errormode = 0;
for i <=7

LOAD [User ID]as [PEFAC User ID],
     [County Code] as [PEFAC County Code],
       trim(Date([Login Date],'MM/DD/YYYY hh:mm:ss')) as [PEFAC Login Date],
     MonthName(Date([Login Date],'mm/dd/yyyy hh:mm:ss')) as PEFAC_MonthYear,
     [Login Date]
FROM
[$(qvwLocation)\Source\PEFAC_Login*.xlsx]
(ooxml, embedded labels , table is Sheet $(i));

exit script;

Re: Scripting Help

sunny_talwar — Mon, 28 Nov 2016 18:57:54 GMT

Try this:

set errormode = 0;
FOR i = 1 to 7

LOAD [User ID]as [PEFAC User ID],
     [County Code] as [PEFAC County Code],
       trim(Date([Login Date],'MM/DD/YYYY hh:mm:ss')) as [PEFAC Login Date],
     MonthName(Date([Login Date],'mm/dd/yyyy hh:mm:ss')) as PEFAC_MonthYear,
     [Login Date]
FROM
[$(qvwLocation)\Source\PEFAC_Login*.xlsx]
(ooxml, embedded labels , table is Sheet$(i));

NEXT i

exit script;

Re: Scripting Help

its_anandrjs — Mon, 28 Nov 2016 19:02:57 GMT

How you know there is 7 sheets on the excel file.

Regards

Anand

Re: Scripting Help

jpenuliar — Mon, 28 Nov 2016 19:08:24 GMT

try something similar to this :

Load data from multiple worksheets in multiple Excel workbooks

Re: Scripting Help

its_anandrjs — Mon, 28 Nov 2016 19:10:12 GMT

One way you can try is last number for the for loop entered maximum number say you have max 20 sheets on the excel file.

set errormode = 0;

FOR i = 1 to 20

LOAD [User ID]as [PEFAC User ID],

[County Code] as [PEFAC County Code],

trim(Date([Login Date],'MM/DD/YYYY hh:mm:ss')) as [PEFAC Login Date],

MonthName(Date([Login Date],'mm/dd/yyyy hh:mm:ss')) as PEFAC_MonthYear,

[Login Date]

FROM

[$(qvwLocation)\Source\PEFAC_Login*.xlsx]

(ooxml, embedded labels , table is Sheet $(i));

NEXT i

Regards

Anand