Help needed - dynamic load script with a variable

[meyham2001]

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

[meyham2001]

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:

[meyham2001]

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]

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]

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]

Variable names are case sensitive.

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

[Peter_Cammaert]

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

[meyham2001]

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 · Accepted solution]

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]

Thank you Peter. It worked like charms.