How to uniformize Headers in mass Load, determined by a list

[hamiltonverkrusse]

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

[Oleg_Troyansky]

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!

[hamiltonverkrusse]

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.

[marcus_sommer]

The renaming-logic could be also placed before the load by using appropriate ALIAS statements.

[hamiltonverkrusse]

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.

[marcus_sommer]

I meant this statement: Alias | Qlik Cloud Help which you defines before you call the load.

[hamiltonverkrusse]

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

[marcus_sommer]

Maybe with something like:

t: load concat('[' & old & ']' ' as ' & '[' & new & ']', ', ') as AliasString from Source;

let v = fieldvalue('AliasString', 1);

Trace $(v); 

ALIAS $(v);

[hamiltonverkrusse]

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

[marcus_sommer]

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