Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rpayn01215
Contributor

Loop Help Please

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Loop Help Please

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.

View solution in original post

4 Replies
Partner
Partner

Re: Loop Help Please

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.

rpayn01215
Contributor

Re: Loop Help Please

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?

Partner
Partner

Re: Loop Help Please

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.

View solution in original post

rpayn01215
Contributor

Re: Loop Help Please

This is perfect. Thanks!