Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jukah_do
Contributor III
Contributor III

Renaming multiple fields

Hello,

I have over 5000 files with similar data that I want to concatenate. However, they have a different number of fields and different field names (the field name contains the name of the file):

Each file has from 2 to max 30 fields. Here a small example:

location_file304589type_file304589user_file304589
UK7UserA

 

location_file93458type_file93458user_file93458day_file93458
US8UserBMonday

 

location_file34jkshdf4user_file34jkshdf4
NLUserC

 

The outcome should be like this:

locationtypeuserday
UK7UserA 
US8UserBMonday
NL UserC 

 

Is this somehow possible without loading the files separately and renaming the fields. Thanks!

7 Replies
Vegar
MVP
MVP

How are you fetching the 5000 files? Are you looping through then one by one or are you using wildcards in your source destination like LOAD ... FROM *.CSV ?

If you are looping through the files then you would know the unique file name string. By knowing this string you could use the ALIAS feature.

Master:

Load * inline [

location_file, type_file

];

//Your LOOP

ALIAS location_file$(vYourFileName) as location_file;

ALIAS type_file$(vYourFileName) as type_file;

Tmp:

Load * from $(vYourFileName).csv;

Concatenate (master) load * resident Tmp;

Drop table Tmp;

//NEXT LOOP

 

Read more about ALIAS here: https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularS...

jukah_do
Contributor III
Contributor III
Author

Thank you for your comment!

To be honest, I am not sure how to do the load. The problem is that I don't know the names of all fields and new field names might be added in future.

So I think I need a solution that loads each table individually, removes the text after the underscore in the field name, and concatenates to the previously loaded table. Does this sound like something that can be done? 😕

Vegar
MVP
MVP

The future new unknown field names adds a new level of complexity to your problem.

I still think my previous suggested solution using ALIAS will hold, but you will need to create the list of aliases dynamically by looking at which headers are available in the file. You will find tips on how to loop through the field names by searching in this community.

You can also use the RENAME FIELD as an alternative to ALIAS, you will still need to loop through the file's field names and you'll have to make sure the rename does not conflict with the field names in your final master table.

Good luck
-Vegar
marcus_sommer

I think I would go with a main-approach like the following:

for each file in filelist('YourPath\*.csv')
   table: load * from [$(file)] (txt, …);
   renameMap: mapping load fieldname(recno(), 'table'),  subfield(fieldname(recno(), 'table'), '_', 1)
      autogenerate nooffields('table');
   rename fields using renameMap;
   if filesize('table.qvd') then
      concatenate(table) load * from table.qvd (qvd);
   end if
   store table into table.qvd (qvd);
   drop tables table;
next

whereby I'm not sure if I would really concatenate 5000 times the already transformed data in the loop and storing the whole amount of data again and again else storing each table in a separate qvd and loading afterwards from there.

- Marcus

jukah_do
Contributor III
Contributor III
Author

Thank you Vegar and Marcus for the responses!

@marcus_sommer, I tried your script but Rename Fields works only for the first table. I did some research and it turns out that this is a limitation as described in the help section:


Limitations:  

You cannot rename two fields to having the same name.

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularS...

This turns out to be much more complex than I expected. 😕

marcus_sommer

That's right - you couldn't rename a field to an already existing field. But for this I added the:

drop tables table;

at the end from the loop and then the approach should work again.

- Marcus

jukah_do
Contributor III
Contributor III
Author

Thank you Marcus, I must be doing something wrong then.

I will spend more time on this.