topic Re: How to uniformize Headers in mass Load, determined by a list in App Development

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

hamiltonverkrusse — Wed, 23 Aug 2023 14:58:28 GMT

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

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

Oleg_Troyansky — Wed, 23 Aug 2023 15:30:02 GMT

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!

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

hamiltonverkrusse — Wed, 23 Aug 2023 16:00:40 GMT

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.

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

marcus_sommer — Wed, 23 Aug 2023 17:08:37 GMT

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

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

hamiltonverkrusse — Thu, 24 Aug 2023 08:53:47 GMT

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.

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

marcus_sommer — Thu, 24 Aug 2023 09:24:16 GMT

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

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

hamiltonverkrusse — Thu, 24 Aug 2023 09:32:42 GMT

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

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

marcus_sommer — Thu, 24 Aug 2023 09:42:11 GMT

Maybe with something like:

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

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

Trace $(v);

ALIAS $(v);

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

hamiltonverkrusse — Tue, 29 Aug 2023 13:40:07 GMT

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

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

marcus_sommer — Tue, 29 Aug 2023 14:30:36 GMT

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

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

hamiltonverkrusse — Tue, 29 Aug 2023 14:44:20 GMT

Thanks agin for your help, but not sure to understand your point. Here is an example of what I want to do :

Table1
PU	CC	Group	User	File
15	152	G1		Table1
12	152		Adupont	Table1
Table2
PU	CostCenter	Group	User	File
22	152	G3		Table2
11	152		Alefebvre	Table2
Alias Table
TableName	Name	UniqueName
Table1	PU	Parent.PU.UniqueName
Table1	CC	CC.UniqueName
Table1	Group	Group
Table1	User	User
Table2	PU	PU.UniqueName
Table2	CostCenter	CC.UniqueName
Table2	Group	Group
Table2	User	User
Table3	Montant	Total.Amount
Table3	OI	OI.UniqueName
Table3	List	ApproverList

Result
Parent.PU.UniqueName	PU.UniqueName	CC.UniqueName	Group	User	File
	22	152	G3		Table2
	11	152		Alefebvre	Table2
15		152	G1		Table1
12		152		Adupont	Table1

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

marcus_sommer — Tue, 29 Aug 2023 15:12:20 GMT

To conclude: you are starting with a pre-load containing file/table/field-information which are read and prepared to define ALIAS strings and maybe also other load-information, like field-lists, where-clauses, what ever.

Some of them like the ALIAS may be applied before the real file-loading is performed but if any of these information isn't globally true else depending on current file/table you will need to detect which file/table is the current one and then providing the specialized information. And for such a task you will need any kind of loop and within it measures to detect/extract which is the current one and then providing the right information.

I'm not sure if I understand what's exactly the problem. If it's related to the ALIAS which might be differentiated in some way you may need to apply it multiple times. I think it's rarely used and I do not remember of having seen a way to remove/disable/revert the statement directly but I think it could be overwritten with new definitions - and in such case you may need to use a filelist-loop and doing the needed work within it.

Beside this I suggest to consider to simplify the approach by loading a file and applying the wanted renaming and then storing it as qvd and dropping the file and going then to the next file. Afterwards all the qvd's could be loaded again.

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

hamiltonverkrusse — Tue, 29 Aug 2023 15:28:40 GMT

To sumup : I want to apply a specific ALIAS depending on the file I'm loading.

In my example, for Table1 we must have ALIAS "PU" as "Parent.PU.UniqueName" and for Table2 we must have ALIAS "PU" as "PU.UniqueName". So, my idea was to prefix all "old headers" with the filename and then to concatenate my paramter fields to use : ALIAS "Table1PU" as "Parent.PU.UniqueName" and ALIAS "Table2PU" as "PU.UniqueName".

Do you think there is a simplier way ?

Thanks again ! really appreciate your help

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

marcus_sommer — Tue, 29 Aug 2023 15:56:08 GMT

I think the above mentioned approach of loading each single file + applying the transformation and storing it + dropping the table and then going to the next file is the most simple approach. You wouldn't need the ALIAS else you could apply a renaming per mapping - and you would have all files as qvd's.

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

hamiltonverkrusse — Wed, 30 Aug 2023 06:26:51 GMT

Ok, will try, what do you think is the simpliest way ?

I have a folder in which I want to load all files (never know wich ones) and for all files, I want to load all fields (nerver know which ones) and rename its

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

marcus_sommer — Wed, 30 Aug 2023 07:36:46 GMT

Like always there is no generally simplest approach else it will depend on various factors which way is more suitable as the others. Further there must be always some information/rules/condition which kind of data exists and what should happens with them - maybe not complete ones at this stage but for the following stages. To not considering the later requirements is surely possible but it may lead to more efforts and complexity within the following layers and the overall efforts might be not optimal.

Assuming that really no valid information exists about these files I would just load them + collecting some meta-information and storing them as qvd-files without any transformation - means also no renaming. For this I would apply a filelist-loop like hinted above and loading all fields with a wildcard. Further I would add path + filename information extracted from the iterator-variable or per functions like filename() respectively if there are further information encoded like YYYYMM periods or any kind of categories/countries or whatever and then also adding the filetime() and filesize() - and using those information to decode the qvd-filenames.

Afterwards you would be able to extract many of these information without loading the file again. Of course accessing the encoded information from the filename is simple but qvd's allow also a read of various meta-information like the qvdnooffields() or qvdnoofrecords() without a load.

Of course more is possible if you creates at ones a meta-table within the loop directly after the file-load, like:

Meta: load '$(file)' as File, now() as LoadTime, .... autogenerate 1;

which is later also saved as qvd. Each interesting kind of information could be saved there like a list of the fields, formatting and NULL information and ... Further these information might be used to categorize the files in different types which you also may encode within the qvd-name.

In the next layer you could then loop through such meta-table and matching those information with a certain set of rules to perform the needed transformation.