topic Re: Loop Help Please in QlikView

Loop Help Please

Anonymous — Fri, 11 Nov 2016 18:47:20 GMT

Hi,

I'm simply trying to load in these multiple Excel sheets into one QVD. The setup is a more manual approach without having a macro to scan the directory before hand. No big deal as this is a one-time load. I'm struggling with the loop part. Please advise on how i can make this work. It won't heed the "do while" piece and keeps trying to loop on into infite. I'd like to try to make the intial load be a table named Historicals and concatenate the follow-up loads onto that (set vConcat). Any input on that part would be appreciated too:

---------------------------------------------------------------------------------------------------------------------------------------------------------------

let i = 24;

do while $(i) <= 52

//set vConcat = ;

//$(vConcat)

Historicals:

LOAD

Mid(SubField([Labor Account], '/', 3), 4, 6) AS [Cost Center],

[Pay Code],

Money,

Hours,

Wages,

Days,

$(i) as FiscalWeek

//date($(vRipDate)) as CalendarDate

FROM

[..\..\Data\FlatFiles\Historicals\2015 ADP Week $(i).xls] //$(i)

(biff, embedded labels, header is 9 lines, table is Sheet1$)

Where not IsNull([Labor Account]) //<> '' to get rid of blank cost centers? //not isnull() is probably doing nothing

;

Let i=$(i)+1;

//set vConcat = Concatenate;

LOOP;

//STORE * from Historicals into ..\..\Data\QVDs\ADP_Data_2015.QVD (qvd);

//DROP Table Historicals;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks a lot!

-Ron

Re: Loop Help Please

fernando_tonial — Fri, 11 Nov 2016 18:57:16 GMT

You can try this.

Just one load for all files.

Change

[..\..\Data\FlatFiles\Historicals\2015 ADP Week $(i).xls] //$(i)

For

[..\..\Data\FlatFiles\Historicals\2015 ADP Week*.xls]

Historicals:

LOAD

Mid(SubField([Labor Account], '/', 3), 4, 6) AS [Cost Center],

[Pay Code],

Money,

Hours,

Wages,

Days,

$(i) as FiscalWeek

//date($(vRipDate)) as CalendarDate

FROM

[..\..\Data\FlatFiles\Historicals\2015 ADP Week*.xls]

(biff, embedded labels, header is 9 lines, table is Sheet1$)

Where not IsNull([Labor Account]) //<> '' to get rid of blank cost centers? //not isnull() is probably doing nothing

;

Don't Worry, be Qlik.

Tonial.

Re: Loop Help Please

Anonymous — Mon, 14 Nov 2016 14:41:18 GMT

Hi Fernando,

Thank you for the suggestion but i need the $(i) variable to populate the FiscalWeek so that i can link the data with our fiscal calendar. I was using a VB script that scans the directory ahead of time to read all of the file names. I was trying to think of a way to strip the week number off the end of the file and fill it in the data load that way. Any thoughts there?

Re: Loop Help Please

fernando_tonial — Thu, 17 Nov 2016 17:10:31 GMT

Hi Ron,

You can use this functions, SubField() and FileBaseName().

Like this:

Historicals:

LOAD

Mid(SubField([Labor Account], '/', 3), 4, 6) AS [Cost Center],

[Pay Code],

Money,

Hours,

Wages,

Days,

SubField(FileBaseName(),' ',-1) as FiscalWeek

//date($(vRipDate)) as CalendarDate

FROM

[..\..\Data\FlatFiles\Historicals\2015 ADP Week*.xls]

(biff, embedded labels, header is 9 lines, table is Sheet1$)

Where not IsNull([Labor Account]) //<> '' to get rid of blank cost centers? //not isnull() is probably doing nothing

Don't worry, be Qlik.

Tonial.

Re: Loop Help Please

Anonymous — Thu, 17 Nov 2016 18:57:37 GMT

This is perfect. Thanks!