Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marcelo_7
Creator
Creator

How do I add a dynamic field to a table?

I have an Excel sheet with two columns; URL and CITY. I am reading in the URL (which leads to an xml) and loading the data from each. But The xml doesn't have a city name so that is why I've added the city name in the Excel. I can read in the urls fine with a loop:

let vCounter = fieldvaluecount('field');

for i=1 to $(vCounter)

let vExcelUrlValue = FieldValue('url',$(i));

let vExcelCityValue = FieldValue('city',$(i));

table:

LOAD xx,

          yy,

          zz,

    $(vExcelCityValue) as city // This is the part that doesn't work.

FROM $(vExcelUrlValue) ;

next

If I type 'Chicago' instead of $(vExcelCityValue) it works but I want it to by dynamic by getting the cityname from the Excel-document.

I would really appreciate some help. Thanks!

1 Solution

Accepted Solutions
Not applicable

Try putting quotes around the dollar sign expansion like:

table:

LOAD xx,

          yy,

          zz,

    '$(vExcelCityValue)' as city

FROM $(vExcelUrlValue) ;

View solution in original post

4 Replies
Not applicable

Try putting quotes around the dollar sign expansion like:

table:

LOAD xx,

          yy,

          zz,

    '$(vExcelCityValue)' as city

FROM $(vExcelUrlValue) ;

adnan_rafiq
Partner - Creator II
Partner - Creator II

You can use Basefilename function to get this city name.

I guess its variable that is creating some problem.

Try this

table:

LOAD xx,

          yy,

          zz,

    Filebasename() as city

FROM $(vExcelUrlValue) ;

or

table:

LOAD xx,

          yy,

          zz,

    Filebasename() as city

FROM *.xls ; (if all files are placed in one folder)

marcelo_7
Creator
Creator
Author

Thanks this worked!

marcelo_7
Creator
Creator
Author

I needed the name of a field in a file not the filename but I will remember that solution. In case I have many files named something I need this will be very useful, thanks!