Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

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
Highlighted
Not applicable

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

Highlighted

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

Highlighted
Not applicable

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