Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!!!!
$-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
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
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
$-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
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