Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

meyham2001
New Contributor

Help needed - dynamic load script with a variable

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);

Tags (1)
1 Solution

Accepted Solutions

Re: Help needed - dynamic load script with a variable

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.

13 Replies
meyham2001
New Contributor

Re: Help needed - dynamic load script with a variable

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: Capture1.PNG

meyham2001
New Contributor

Re: Help needed - dynamic load script with a variable

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

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

meyham2001
New Contributor

Re: Help needed - dynamic load script with a variable

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

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

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

meyham2001
New Contributor

Re: Help needed - dynamic load script with a variable

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

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.

meyham2001
New Contributor

Re: Help needed - dynamic load script with a variable

Thank you Peter. It worked like charms.

Community Browser