topic Re: Dynamic Fields Name In Excel in QlikView

Dynamic Fields Name In Excel

ariel_klien — Wed, 18 Nov 2015 16:47:28 GMT

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

Re: Dynamic Fields Name In Excel

sunny_talwar — Wed, 18 Nov 2015 16:50:03 GMT

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

Gysbert_Wassenaar — Wed, 18 Nov 2015 17:04:49 GMT

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;

Re: Dynamic Fields Name In Excel

Mark_Little — Wed, 18 Nov 2015 17:09:19 GMT

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

Re: Dynamic Fields Name In Excel

maxgro — Wed, 18 Nov 2015 17:36:46 GMT

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

MarcoWedel — Thu, 19 Nov 2015 01:12:39 GMT

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

Re: Dynamic Fields Name In Excel

ariel_klien — Wed, 16 Dec 2015 07:18:35 GMT

Really Nice One!!!!

Thanks!