How do I add a dynamic field to a table?

[marcelo_7]

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!

[#2 · Accepted solution]

Try putting quotes around the dollar sign expansion like:

table:

LOAD xx,

          yy,

          zz,

    '$(vExcelCityValue)' as city

FROM $(vExcelUrlValue) ;

[adnan_rafiq]

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]

Thanks this worked!

[marcelo_7]

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!