Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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