BINARY load with dynamic file name

[jcamps]

Hi,

I have an environment where a UX.QVW needs to import the data structure from a Data.QVW using a binary load.

That would be very easy if the QlikMart file always had the same name - but it doesn't. The reasons are a little harder to explain, but for the purpose of simplicity, let's assume it's just a YearMonth suffix, for example Data_201505.QVW.

I need to get the BIINARY LOAD code right. I cannot use any other script sentences before to calculate the filename, so I expect dollar sign to be the solution, but cannot get it right. Something like:

BINARY QM_$(=year(today()*100+month(today())).qvw

... just does not work... it seems that the dollar sign with equal sign is not properly evaluated. It does work in an expression in a text box for example, but when debugging it in the load script, the $(=...) just evaluates to empty string or worse to '(internal error)'.

There might already be a solution in the community but I cannot find it...

Any ideas? Thanks in advance!

- J

[petter]

You expression should work if you do this:

BINARY_QM_$(=Year(Today())&Date(Today(),'MM')).qvw;

[maxgro · Accepted solution]

I think with another .qvw, all files in same dir

a1.qvw           // make an include with a dynamic binary load

DIRECTORY;

let name = 'binary a_' & date(today(), 'YYYYMM') & '.qvw;';

tmp: load 0 as [$(name)]

AutoGenerate 0;

store tmp into a1.inc (txt);

output is a1.inc

a2.qvw           // binary load

$(Must_Include=a1.inc);

[jcamps]

Hi Petter and thanks for the feedbackk, unfortunately it doesn't work, I'm consistently getting the same behavior in any expression I try with $(=...).

If used in the binary sentence, a "file not found" error pops up, and the filename is just as if the $(=...) returned empty string, see:

My original expression had the same behavior.

I've tried putting it into a variable like so:

     ///$tab Main

     set x = BINARY_QM_$(=Year(Today())*100 + month(Today())).qvw;

It does not work either, in the debugging trace is shows that the dollar sign evaluates to blank:

     set x = BINARY_QM_.qvw

Whereas if i put in a Text Box in a sheet, the same expression:

     ='BINARY_QM_$(=Year(Today())*100 + month(Today())).qvw'

I get the expected result:

     BINARY_QM_201506.qvw

Did I miss anything and the $(=...) expansion does NOT work on the script? If so... how can I achieve a binary load from dynamic filenames?

Thanks,

[petter]

It just tried the expression that it fully evaluated into a string that should give the filename... but let me give it a try with a real BINARY quickly and I'll come back to you...

[rwunderlich]

I don't think the "=" within $() is a technique available in script. You can however reference a variable if you can pass the variable in.

[jcamps]

Very nice trick / workaround Massimo!

I elaborated on your idea, since having the binary load on an external INC file was not a very secure strategy (a missing or deleted file would result in a unsecure UX document)

What I finally did is:

a) The DATA_YYYYMM.qvw creates, on its own reloading, the a1.inc file. (using Massimo's code)

b) On the hidden script of the UX, i'm doing a BINARY Data_$(=Must_include=a1.inc). That way if the file is wrong it just won't load at all, keeping security at bay.

Thx,

- J

[jcamps]

Hi Rob, thanks... that clarifies the behavior! A pity .

I should mark your answer as correct, for future or other people's reference... but I think Massimo above came out with a workaround to overcome the script syntax limitation...

Thanks again,

[chetanpatel]

Cold  you please share your Qlick files