topic How do I add a dynamic field to a table? in QlikView

How do I add a dynamic field to a table?

marcelo_7 — Sat, 12 May 2012 02:57:45 GMT

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) ;

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!

How do I add a dynamic field to a table?

Sat, 12 May 2012 05:09:33 GMT

Try putting quotes around the dollar sign expansion like:

table:

LOAD xx,

yy,

zz,

'$(vExcelCityValue)' as city

FROM $(vExcelUrlValue) ;

How do I add a dynamic field to a table?

adnan_rafiq — Sat, 12 May 2012 08:08:54 GMT

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) ;

table:

LOAD xx,

yy,

zz,

Filebasename() as city

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

How do I add a dynamic field to a table?

marcelo_7 — Sat, 12 May 2012 13:36:08 GMT

Thanks this worked!

How do I add a dynamic field to a table?

marcelo_7 — Sat, 12 May 2012 13:39:20 GMT

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!