Merging/Combining multiple tables with programmatic field rename

[jay_pee]

Hi all,

I have 3 tables of data, all of which have identical fields apart from a prefix which is specific to each source table. I'd like to merge the content of these 3 tables without having to list out all the fields using aliases if possible. Source data looks like this 

AAA.FieldName1
AAA.FieldName2
AAA.FieldName3...

BBB.FieldName1
BBB.FieldName2
BBB.FieldName3...

CCC.FieldName1
CCC.FieldName2
CCC.FieldName3..

Is it possible to merge the fields inside a for loop using rename and resident load or something so that I end up with the following...

TEST.FieldName1     (Has the merged values from AAA.FieldName1, BBB.FieldName1, CCC.FieldName1)
TEST.FieldName2     (Has the merged values from AAA.FieldName2, BBB.FieldName2, CCC.FieldName2)
TEST.FieldName3     (Has the merged values from AAA.FieldName3, BBB.FieldName3, CCC.FieldName3) and so on...

Thanks a lot

[Oleg_Troyansky · Accepted solution]

Hi Jay,

So, I'd say - yes, it's possible, but it's not too simple... The RENAME FIELDS command wouldn't allow merging several fields into one by renaming.

So, the only viable way is to load (or reload) the source data and rename each field using the AS keyword.

Now, you can write a loop over the existing fields in each table, and generate the renaming code programmatically. For that, you will need to use the Table functions that allow you to operate with field names in a table that was already loaded into Qlik:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/TableFunctions...

So, the logic of this loop would be this - for each existing field, add another line with the renaming logic into a variable. At the end, perform a RESIDENT load with the generated variable in a $-sign expansion.

I'm teaching somewhat similar techniques in my book, you may want to check it out.

Cheers,

[marcus_sommer]

Far more simple and saving a lot of efforts would be to avoid the qualifying at the beginning.