Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ariel_klien
Valued Contributor

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

Re: Dynamic Fields Name In Excel

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

Re: Dynamic Fields Name In Excel

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;

Re: Dynamic Fields Name In Excel

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
mark6505
Valued Contributor III

Re: Dynamic Fields Name In Excel

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

MVP
MVP

Re: Dynamic Fields Name In Excel

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;

Re: Dynamic Fields Name In Excel

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
Valued Contributor

Re: Dynamic Fields Name In Excel

Really Nice One!!!!

Thanks!

Community Browser