topic Re: How to convert Multiple fields in to Single field dynamically in App Development

How to convert Multiple fields in to Single field dynamically

SatyaPaleti — Tue, 14 Jun 2022 15:23:46 GMT

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

Re: How to convert Multiple fields in to Single field dynamically

stevedark — Tue, 14 Jun 2022 20:35:16 GMT

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