Dynamic Fields Name In Excel

[ariel_klien]

Hello All Experts,

I have 2 Excels Files:

1) Fields Names

2) Data

i attached for example

today i'm doing this code:

FieldName:

LOAD A

FROM [field Name.xlsx] (ooxml, no labels, table is Sheet1);

for i=1 to NoOfRows('FieldName')

       let vName$(i)=Peek('A',i-1,'FieldName');

next i;

Data:

LOAD A as '$(vName1)',

          B as '$(vName2)',

          C as '$(vName3)',

          D as '$(vName4)'

FROM Data.xlsx (ooxml, no labels, table is Sheet1);

drop Table FieldName;

It is work fine but i need something dynamic.

If in the excel file i will add another field - i need manually to change the code in the Script.

I want automatic reload.

Any Idea?

Ariel

[sunny_talwar]

Try something like this:

Table:

LOAD Concat(@1, '|', Order) as List;

LOAD @1,

  RecNo() as Order

FROM

Test.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Transpose()

));

LET vList = Chr(39) & Peek('List') & Chr(39);

DROP Table Table;

FinalTable:

LOAD *

FROM

Test.xlsx

(ooxml, no labels, header is 1 lines, table is Sheet1);

For i = 1 to NoOfFields('FinalTable')

  LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';

  LET vFieldNew = '[' & SubField($(vList), '|', $(i)) & ']';

  RENAME Field $(vFieldOld) to $(vFieldNew);

NEXT i;

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

FieldMap:

mapping LOAD Chr(RecNo()+Ord('A')-1) as OldName, A as NewName

FROM [FieldNames.xlsx] (ooxml, no labels, table is Sheet1);

Data:

LOAD * FROM [Data.xlsx] (ooxml, no labels, table is Sheet1);

RENAME Fields using FieldMap;

[Mark_Little]

HI,

If it is friendly names you are trying to do, this is how i have approached it in the past,

Also a little self promotion.

Friendly Name using Rename.docx

Mark

[maxgro]

maybe

FieldName:

LOAD A as NEWFIELDNAME FROM [field Name.xlsx] (ooxml, no labels, table is Sheet1);

Data:

LOAD * FROM Data.xlsx(ooxml, no labels, table is Sheet1);

for i=1 to NoOfFields('Data')

  let vNew$(i)=Peek('NEWFIELDNAME',i-1,'FieldName');

  let vOld$(i)=FieldName(i, 'Data');

  trace $(vNew$(i)); trace $(vOld$(i));

  RENAME Field $(vOld$(i)) to $(vNew$(i));

next i;

[MarcoWedel]

Hi,

nice one.

some similar, yet a bit longer solution that should work for more than 26 columns might be:

mapFieldName:
Mapping LOAD RecNo(), * FROM [https://community.qlik.com/servlet/JiveServlet/download/914855-197404/field%20Name.xlsx] (ooxml, no labels, table is Sheet1);

Data:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/914855-197403/Data.xlsx] (ooxml, no labels, table is Sheet1);

tabTemp:
CrossTable(field,temp) LOAD 1,* Resident Data Where RecNo()=1;

mapFieldName2:
Mapping LOAD field, ApplyMap('mapFieldName',RecNo()) Resident tabTemp;

DROP Table tabTemp;

RENAME Fields using mapFieldName2;

hope this helps

regards

Marco

[ariel_klien]

Really Nice One!!!!

Thanks!