Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
meyham2001
Contributor
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);

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

View solution in original post

13 Replies
meyham2001
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Contributor
Contributor
Author

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Variable names are case sensitive.

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

meyham2001
Contributor
Contributor
Author

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Contributor
Contributor
Author

Thank you Peter. It worked like charms.