topic Re: how can i create list of fields name dynamically? in QlikView

how can i create list of fields name dynamically?

Tue, 22 Sep 2015 07:40:55 GMT

Hi,

I have many number of excel files i have to add a field i. fieldlist in all of these files dynamically in loop.how can i do that??.

I have tried this by the following code in script.And i want to capture field not found error in the other table how can i achieve this.please help me anybody. Here i am attaching my code which i have written for this please have a look on it.

FOR Each File in FileList('C:\Users\vasudha\Documents\TestExce\*.xlsx')

tab:

LOAD

Data,

Value,

' ' as FieldList

FROM

'$(File)'

(ooxml, embedded labels, table is Sheet$(i));

join(tab)

load

* Inline [

FieldList

Data,

Value ];

if ScriptError = 11 then

newtab:

Load *,

fName& '|' &SheetNumber& '|' &FieldList as FieldKey;

LOAD

FieldList,

FileName() as fName,

$(i) as SheetNumber,

'$(File)' as FilePath

FROM

'$(File)'

(ooxml, embedded labels, table is Sheet$(i), filters(Transpose()));

ENDIF

next File

Re: how can i create list of fields name dynamically?

Anonymous — Tue, 22 Sep 2015 07:56:40 GMT

Hi Vasudha,

Manually enter below line in script

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Source\YourExcelFileName.xlsx];

XlsTables:
SQLTables;

//XlsTables: and SQLTables will create field TABLE_NAME with list of sheetnames as rows.

//the below code will loop thru this rows and loads data from the sheets in excel file.

LET vRows = NoOfRows('XlsTables');

FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);

$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

Hope this helps

Regards

Neetha

Re: how can i create list of fields name dynamically?

MarcoWedel — Tue, 22 Sep 2015 23:21:17 GMT

Hi,

this should give you a field list of your files:

tabFields:
CrossTable (MyFieldName, MyFieldValue)
LOAD FileBaseName() as File, *
FROM [*.xlsx] (ooxml, embedded labels, table is MySheetName)
Where RecNo()=1;

hope this helps

regards

Marco

Re: how can i create list of fields name dynamically?

Wed, 23 Sep 2015 05:33:50 GMT

Hi MarcoWedel,

Thanks for your reply.

I have tried your above solution.But its not giving me all numbers of fields in the table let say i have 5 fields in my table it is skiping first field in the MyFieldName column.How can i have all the column names.Please let me know.

For ex.

Tab:

CrossTable (MyFieldName, MyFieldValue)

Load Field1,

Field2,

Field3,

Field4,

Field5

FROM

(ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);

in the above example it is skiping first field i.e Field1 in MyFieldName column.

Regards,

Vasudha

Re: how can i create list of fields name dynamically?

MarcoWedel — Wed, 23 Sep 2015 10:52:19 GMT

Crosstable needs at least one qualifier field, that's why I added a filename field before loading the other fields using '*'.

If you don't need the filename information, you could as well load a dummy field at first like:

LOAD 1, *

FROM ...

regards

Marco

Re: how can i create list of fields name dynamically?

vikasmahajan — Wed, 21 Oct 2015 12:27:10 GMT

Can you attach sample qvw for the I have same requirement

Vikas