Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
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
Highlighted

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

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
Highlighted
neetha_p
Honored Contributor

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

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

Highlighted

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

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

Highlighted
Not applicable

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

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

Highlighted

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

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

Highlighted
vikasmahajan
Esteemed Contributor

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

Can you attach sample qvw for the I have same requirement

Vikas