Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
fernando_tonial
Valued Contributor

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.

4 Replies
fernando_tonial
Valued Contributor

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?

fernando_tonial
Valued Contributor

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.

rpayn01215
Contributor

Re: Loop Help Please

This is perfect. Thanks!