Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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
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;
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
Really Nice One!!!!
Thanks!