Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!!!!

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

$-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

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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
Former Employee
Former Employee

$-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

Not applicable
Author

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