topic Re: Help needed - dynamic load script with a variable in QlikView

Help needed - dynamic load script with a variable

meyham2001 — Tue, 15 Mar 2016 13:30:24 GMT

Dear community I need help.

I'm trying to automate process of loading the newest xlsx file in qvw and i wrote a script that should do just that.

I'm stuck at the point where I need to transfer value stored in variable to the path of loaded xlsx. I've tried everything. Using LET, SET...

Plesae help me because I'm stuck here...

Code:

SET vMaxDate = MAX(Mid(FN.Filename,Len(FN.Filename)-11,8));

//Load Phonebook entries

AC_temp:

LOAD

Lower(Email) AS %email,

DN,

PersonnelID,

FullName,

FamilyName,

ChristianName,

Country,

OU,

OUName,

PhoneNumber,

MobileNumber,

Company,

Position,

DivisionID,

DivisionName,

All_OUs,

OU_Count

FROM

C:\Users\mydocuments\Qlik_Training\BL_obsolete\DEV\B_L_obsolete\0150_RawData\CSV\Phonebook.$(vMaxDate).csv

(txt, codepage is 1250, embedded labels, delimiter is ';', msq);

Re: Help needed - dynamic load script with a variable

meyham2001 — Tue, 15 Mar 2016 14:25:46 GMT

Thank you for the fast reply, but unfortunately that didn't fix the issue. When I try your way I get an error message as follows:

Re: Help needed - dynamic load script with a variable

meyham2001 — Tue, 15 Mar 2016 14:34:46 GMT

When i run debugger and it comes to the variable:

LET vMaxDate = MAX(Mid(FN.Filename,Len(FN.Filename)-11,8)); it states that it stores null value.

Re: Help needed - dynamic load script with a variable

Peter_Cammaert — Tue, 15 Mar 2016 15:02:27 GMT

Max() is an aggregation function, in a script to to be used in a LOAD statement.

If FN.Filename is a field with different file names in a table somewhere; you can use something like this:

MaxFNTable:

LOAD MAX(Mid(FN.Filename,Len(FN.Filename)-11,8)) AS MaxFileNumber

RESIDENT Whatevertable;

LET vMaxDate = num(peek('MaxFileNumber'), '00000000');

but only on condition that the 8-character part that you want to strip from the file names is a real numerical value. If this 8-character field is a date (like DD/MM/YY = also 8 characters), you may have to use a different formatting assembly.

Peter

Re: Help needed - dynamic load script with a variable

meyham2001 — Tue, 15 Mar 2016 15:42:02 GMT

Thank you for your reply, but after I've tried your way I get the same

result which is that when I call variable in the script i get null value.

And when I test value of variable in text box I get correct value.

Re: Help needed - dynamic load script with a variable

Peter_Cammaert — Tue, 15 Mar 2016 16:06:12 GMT

Variable names are case sensitive.

Can you upload an example document that has this particular problem?

Re: Help needed - dynamic load script with a variable

Peter_Cammaert — Tue, 15 Mar 2016 16:07:47 GMT

And post an example of the file names you are trying to use in your script?

Re: Help needed - dynamic load script with a variable

meyham2001 — Tue, 15 Mar 2016 17:02:38 GMT

I've attached my qvw and example files I'm trying to load are:

Phonebook.20140506.csv

Phonebook.20150308.csv

Phonebook.20160102.csv

The poant of the loop in .qvw is to determine which is the latest phonebook and the loop works correctly.

However, the problem is with storing the value (20160102) in variable and passing it to the path so load statement could load from correct phonebook.

I hope this explanation and .qvw will help you to help me.

EDIT: I uploaded new qvw, there was an error in previous one.

Re: Help needed - dynamic load script with a variable

Peter_Cammaert — Thu, 17 Mar 2016 08:19:11 GMT

It seems that you didn't try my solution. Please replace your statement

LET vMaxDate = 'MAX(Mid(FN.Filename,Len(FN.Filename)-11,8))';

(as this won't work outside of a LOAD statement) with this piece of script:

MinMaxTable:

LOAD Max(num(SubField(PB.Filename, '.', -2))) AS MaxDate

RESIDENT PB;

LET vMaxDate = num(peek('MinMaxTable.MaxDate'));

DROP Table MinMaxTable;

Then adjust your LOAD statement to again include $(vMaxDate) in the date part.

Re: Help needed - dynamic load script with a variable

meyham2001 — Thu, 17 Mar 2016 09:08:45 GMT

Thank you Peter. It worked like charms.

Re: Help needed - dynamic load script with a variable

Peter_Cammaert — Thu, 17 Mar 2016 17:40:28 GMT

If your question has been answered to satisfaction, please close the discussion by marking an answer as Correct. That answer will appear in the same frame as your original post at the top, making it easier for other Community Members to find Question & Answer. Thanks.

Peter

Re: Help needed - dynamic load script with a variable

rubenmarin — Thu, 17 Mar 2016 17:47:40 GMT

And marking the correct answer also helps others to not waste time reading an already solved thread.

Re: Help needed - dynamic load script with a variable

meyham2001 — Thu, 17 Mar 2016 21:04:24 GMT

Thank you Peter once more.

Re: Help needed - dynamic load script with a variable

Peter_Cammaert — Fri, 18 Mar 2016 07:45:16 GMT

My pleasure.

Enjoy QlikView and the Qlik Community.