Loop Help Please

[Anonymous]

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

[fernando_tonial]

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.

[Anonymous]

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?

[fernando_tonial · Accepted solution]

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.

[Anonymous]

This is perfect. Thanks!