Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how can i create list of fields name dynamically?

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

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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

MarcoWedel

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

Not applicable
Author

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

MarcoWedel

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

vikasmahajan

Can you attach sample qvw for the I have same requirement

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.