topic Re: Read Loop Element into Column in QlikView

Read Loop Element into Column

matthewjbryant — Mon, 07 Jul 2014 11:01:52 GMT

Hi All,

I am loading multiple sheets from an Excel doc into my report, but need to know which sheet the data comes from. This means I need a column in every row with the sheet name. This is what I'm trying, but it doesn't work:

SET i = JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC;

FOR Each m in $(i);

LOAD

text('$(m)') as Month,

FROM

[DOC.xlsx]

(ooxml, embedded labels, Table is [$(m)]);

The data loads, but Month is blank on every row. How can I do this?

Re: Read Loop Element into Column

marcus_sommer — Mon, 07 Jul 2014 11:20:27 GMT

Your syntax for the variables aren't quite correct - try this:

SET i = "'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'";

FOR Each m in $(i);

LOAD

'$(m)' as Month, rowno() as RowNo,

FROM

[DOC.xlsx]

(ooxml, embedded labels, Table is [$(m)]);

- Marcus

Re: Read Loop Element into Column

matthewjbryant — Mon, 07 Jul 2014 11:33:24 GMT

Hi Marcus Sommer,

Thanks for the reply, but as soon as I place any of the elements past JAN in quotes I get a 'Field not found' error because the sheet cannot be loaded. If I just put JAN in quotes the first 12th of all the row numbers have 'JAN' populated in them.

Re: Read Loop Element into Column

marcus_sommer — Mon, 07 Jul 2014 11:41:44 GMT

Every item in variables-list needs single-quotes and the complete variable needs double-quotes and then it worked - i have tested it.

- Marcus

Re: Read Loop Element into Column

jonathandienst — Mon, 07 Jul 2014 11:51:58 GMT

Does not need the outer ". This is sufficient:

SET i = 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC';

Having said that, it works with or without the outer quotes. However, the inner single quotes are necessary.

HTH

Jonathan

Re: Read Loop Element into Column

matthewjbryant — Mon, 07 Jul 2014 12:04:52 GMT

Thanks Jonathan Dienst and Marcus Sommer for trying to help, but the query only works without any quotes - I too have tested it! The query will run with JAN in quotes, but no more than that for some reason. I guess this is the difference between using a set and using a variable.

Re: Re: Read Loop Element into Column

marcus_sommer — Mon, 07 Jul 2014 12:14:57 GMT

For me it worked - I assume there is somewhere a little difference.

- Marcus

Re: Read Loop Element into Column

sudeepkm — Mon, 07 Jul 2014 12:30:09 GMT

Can you please try like below?

set errormode=0; // setting the error mode to 0

// create a table keeping all the possible sheet names

tmpinline:

load * Inline [

sheetname

JAN

FEB

MAR

];

// load the total count of sheet names

LET vSheetCount = NoOfRows('tmpinline');

// load each files in a loop assume that we have book1 and book2.xlsx

for each vFiles in filelist('Book*.xlsx')

// load each sheet until total sheet counts

for i = 0 to $(vSheetCount)-1

// store the sheet name

let vSheetName = peek('sheetname',$(i),'tmpinline');

LOAD

'$(vSheetName)' as sheetname,FileName() as filename,*

FROM [$(vFiles)] (ooxml, embedded labels, Table is '$(vSheetName)');

NEXT i

next vFiles

drop table tmpinline;

Re: Read Loop Element into Column

matthewjbryant — Mon, 07 Jul 2014 13:17:19 GMT

Thanks for your help Marcus. It turns out the main problem was with the spreadsheet. Once I gave them all names and edited my LOAD to match your script worked a treat. Cheers mate!