How to convert Multiple fields in to Single field dynamically

[SatyaPaleti]

Hi Everyone,

I have following requirement could some one help me to resolve this issue

we are having n no of tables. we have to load all the fields of all the tables and assign it to one field.

Here is the example

NoConcatenate
Demo_Table:
Load
'' as Demo ID
AutoGenerate(0);

Tables:
load
Demo_Tables_test 
from xyz.qvd

FOR i=0 to NoOfRows('Tables')

let vTableName= Peek('Demo_Tables_test ',$(i));

$(vTableName):
LOAD
*
;

Select
*
FROM $(schema).$(vTableName);

Concatenate(Demo_Table)

Final_Tabel

Load *

Resident $(vTableName);

Now all the fields of all tables were coming in to Demo_Table.

All these fields should assign to a single field. For example Field  name Dimension. If the user see Dimension then all the values should appear

Can some one help me to achieve this

Thanks,

Satya 

[stevedark]

Hi @SatyaPaleti 

I don't think you are too far off of what you are trying to achieve. It doesn't need a temporary table and a resident load, just a table stub that you always concatenate to:

Final_Tabel:
LOAD
null() as DropMe
AutoGenerate(1);

Tables:
LOAD
Demo_Tables_test
from [lib://DataFiles/xyz.qvd] (qvd);

FOR i=0 to NoOfRows('Tables') -1

   let vTableName= Peek('Demo_Tables_test ',i, 'Tables');

   CONCATENATE(Final_Tabel)
   LOAD
   *
   ;

   SQL SELECT
   *
   FROM dbo.$(vTableName);
NEXT

DROP TABLE Tables;
DROP FIELD DropMe;
let i =;
let vTableName =;

This will leave a dummy row with no values, which you could remove with a RESIDENT LOAD or a LEFT KEEP statement, but to be honest the null row is not going to cause any harm. You may be able to use AUTOGENERATE(0) in the first statement, but I am not sure it will work.

Hope that helps.

Steve