Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ariel_klien
Specialist
Specialist

Dynamic Fields Name In Excel

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
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

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;


talk is cheap, supply exceeds demand
Mark_Little
Luminary
Luminary

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
MVP
MVP

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
Specialist
Specialist
Author

Really Nice One!!!!

Thanks!