How to resolve $ Dollar-sign expansions in field contents (load script / dimension)

[#1]

Hi all!

I am currently desperate trying to resolve Dollar-sign expansions (e.g. $(xxx)) in my data during the load. What works perfectly fine is using the INLINE statement, i.e. filename gets resolved to "2013-06 Filename.xls":

LET Year = 2013;
LET Month = 06;
LOAD * INLINE [
     FILE_NAME
     $(Year)-$(Month) Filename.xls
];

However, my data is that big in size that is not managable with INLINE load statement. Thus, I am maintaining an Excel file with a single row and filenames noted like above. But here is the problem. The filename is not resolved to "2013-06 Filename.xls" but keeps at "$(Year)-$(Month) Filename.xls".

Directory;
LOAD FILE_NAME
FROM
[Filenames.xlsx]
(ooxml, embedded labels, table is FILES);

I have tried several things:

• $(FILE_NAME) again
• Evaluate(FILE_NAME)

But nothing is working!

How do I do that?

If it is possible during load, can it still be done in the front end with calculated expressions?

Thanks for your help!!!!

[jonathandienst]

Hi

If I have understood your requirements correctly, use:

Directory;

LOAD '$(Year)-$(Month) ' & FILE_NAME

FROM

[Filenames.xlsx]

(ooxml, embedded labels, table is FILES);

Hope that helps

Jonathan

[#3]

Hi Jonathan,

that is not exactly what I meant. Please have a look at the attached XLSX file. $() epansions are present in the cells and not translated/resolved during import in QlikView.

Thanks again!

Bye,
Sven

[hic · Accepted solution]

$-expansions only work if the $() is in the script or in the formula. To change a string found in data you need to use MapSubstring() or ApplyMap().

HIC

[#5]

Hi!

Pefect! MapSubstring() is doing the trick. I have alrady known ApplyMap() but wasn't aware of the other function

Thanks! You made my day!

Bye,

Sven