topic Re: Merging/Combining multiple tables with programmatic field rename in App Development

Merging/Combining multiple tables with programmatic field rename

jay_pee — Wed, 30 Aug 2023 15:12:25 GMT

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

Re: Merging/Combining multiple tables with programmatic field rename

Oleg_Troyansky — Wed, 30 Aug 2023 15:22:36 GMT

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/table-functions.htm

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,

Re: Merging/Combining multiple tables with programmatic field rename

marcus_sommer — Thu, 31 Aug 2023 06:39:06 GMT

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