Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Please check my xls file :
I want to concatenate several tables determining the header depending the file name and the theorical header Name
Can you help me on this ?
Thanks
Hi there,
Check out a script command called RENAME FIELDS. You can load all of your tables and then rename all of the fields at once, using a mapping table, similar to your Excel table.
To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!
Hi,
Thanks for the answer but :
Limitation:
Two differently named fields cannot be renamed to having the same name. The script will run without errors, but the second field will not be renamed.
The renaming-logic could be also placed before the load by using appropriate ALIAS statements.
Here is the code i'm using :
Final:
load 1 as Dummy autogenerate 1;
For i = 0 to NoOfRows('FileList')-1
LET ConnectLoop=Peek('PathName', '$(i)', 'FileList');
FieldNamesMap:
MAPPING LOAD WF_Key,
WF_StringValue&WF_OpérateurLogique
resident wf
//where WF_LookupFileName = '$(vFileName)'
;
RENAME FIELDS USING FieldNamesMap;
concatenate
Final:
load *
,FileName() as LookupFileName
from $(ConnectLoop) (txt, utf8, embedded labels, delimiter is ',', msq);
let vFileName = FileName();
Next i
//drop table FileList;
NoConcatenate
Lookup_final:
Load *
,autonumber(RowNo(),[LookupFileName]) as RowNum
Resident Final where Dummy <>'1';
drop table Final;
drop field Dummy;
So, I'm looping to load my files with * and I can not rename my fileds with alias.
My Goal is after each load to rename header to uniformize.
I meant this statement: Alias | Qlik Cloud Help which you defines before you call the load.
Hi,
Thanks for your help.
For example : Alias ID_N as NameID;
So you mean by this statement, I can force Qlik to rename each field ID_N by NameID ?
If Yes, do you know if I can load it by xls file ?
Thanks again
Maybe with something like:
t: load concat('[' & old & ']' ' as ' & '[' & new & ']', ', ') as AliasString from Source;
let v = fieldvalue('AliasString', 1);
Trace $(v);
ALIAS $(v);
Hi,
Thanks, I'm trying to use this solution. I'm facing one more issue to finalize. For those 2 lines to create the Alias :
Depending on if i'm loading file "3" or file "4", my new header must be different, so I need to prefix my "load *" headers with the file i'm loading, then i'll be able to use Alias "3PU" as "ProcurementUnit.UniqueName" and "4PU" as "ProcurementUnit.Parent.UniqueName"
Do you have any Idea on how to do that ?
4 | PU | ProcurementUnit.UniqueName | |
3 | PU | ProcurementUnit.Parent.UniqueName |
If multiple files needs to be loaded differently you will need to switch between different load-statements. This means you need to branch between multiple versions or you need to adjust one on the fly. In each case this couldn't be done with the classical wildcard-load which is an implicit loop else you need to define it explicitly - which is quite simple and should be IMO the standard-approach. It means something like:
for each file in filelist('path/file*.*')
if keepchar(subfield('$(file)', '\', -1), '0123456789') = 'MyCondition' then
let vLoad = 'MyStatement1';
else
let vLoad = 'MyStatement2';
end if
t: load $(vLoad) from $(file) (fileformat);
next