topic Re: Concatenate Tables with different columns in one New Table in App Development

Concatenate Tables with different columns in one New Table

QFanatic — Mon, 06 Sep 2021 15:12:40 GMT

hi,

I am reading a lot of disparate qvd's from a drive (they do have a few key fields in common) and I'd like to concatenate whatever their content, into 1 new Table.

I'm not sure how to do this, since the layout of the qvd's are different..an auto concatenate wont take place.

If I run my code as it currently stands, I get 1 MyTable, a MyTable-1 and a Syn table - which is no surprise.

Please may you assist?

thanks

for each vX in $(vTables)
MyTable2:
let Path_All = '$(vX)' & '.qvd';

For Each File in filelist(Path_All)
MyTable:
load
*,
'$(File)' as Filename,
left(subfield('$(File)','_',-1) ,6) as Testfield,

'$(vX)' as Main_Table,
'$(Filename)' as Sub_table
from
[$(File)] (qvd)
where
left(subfield('$(File)','_',-1),6) >= '$(vExtractPeriod)';
next File;
next vX;

Re: Concatenate Tables with different columns in one New Table

anthonyj — Tue, 07 Sep 2021 00:33:53 GMT

Hi, @QFanatic ,

I read a fantastic and easy workaround in the answers to another question just the other day. It uses the concatenate function to force the tables together. The trouble with a loop is that you can't add the function before the table is created so using an empty variable you can set the concatenation after the first table is created allowing you to get around this issue.

//Set an empty variable
set vConcatenate =;

for each vX in $(vTables)
MyTable2:
let Path_All = '$(vX)' & '.qvd';

For Each File in filelist(Path_All)

//This will be null on the first run
$(vConcatenate)

MyTable:
load
*,
'$(File)' as Filename,
left(subfield('$(File)','_',-1) ,6) as Testfield,

'$(vX)' as Main_Table,
'$(Filename)' as Sub_table
from
[$(File)] (qvd)
where
left(subfield('$(File)','_',-1),6) >= '$(vExtractPeriod)';

//Set the empty variable to "concatenate" now the first table has been created.
set vConcatenate = concatenate;

next File;
next vX;

I hope this works for you.

Thanks

Anthony

Re: Concatenate Tables with different columns in one New Table

QFanatic — Tue, 07 Sep 2021 07:11:42 GMT

Fantastic workaround, thank you!