[Transformation] Column names -> 1st row, Column values -> 2nd row

[smiling_cheetah]

Hi everyone,

I faced the following issue while loading data from json and couldn't get my head around it. (Wasn't able to find relevant topic here in community, but if there is one, I'll be thankful for a link )

So I have a table like that

Header 1	Header 2	Header 3	Header 4	Header 5
Value 1	Value 2	Value 3	Value 4	And so on...

And want to transform it into a new one

Headers	Values
Header 1	Value 1
Header 2	Value 2
Header 3	Value 3
Header 4	Value 4

Now trying to figure it out by own cognitive abilities, but would really appreciate any help

Thanks

[smiling_cheetah]

So far I've got to the following script, but still wonder if there's a way to optimize the code (now it seems too complicated)

(Table is my initial table).

Table_FIELD:

load * inline [

Field

];

Table_FIELD_VALUE:

load * inline [

FieldName, FieldValue

];

Let vFieldCounts = NoOfFields('Table');

For vField = 1 to $(vFieldCounts)

Concatenate(Table_FIELD)

load FieldName($(vField), 'Table') as Field

AutoGenerate(1);

next vField;

FOR Each vFieldName in FieldValueList('Field')

Concatenate(Table_FIELD_VALUE)

load '$(vFieldName)' as FieldName,

  FieldValue('$(vFieldName)', 1) as FieldValue

Resident Table;

next vFieldName;

[el_aprendiz111 · Accepted solution]

Hi

[Anil_Babu_Samineni]

Use Cross Table while transpose the Data

[smiling_cheetah]

Thank you for the response

Haven't had a chance to try it, since the situation appeared to be little more complicated than I described.

The problem was that the dictionary is not stable and could be updated in future. But RESTConnector doesn't support feature SELECT *, meaning one has to specify names of the fields (which is quite difficult to do not being capable to peek in future ). So I had to find another version of the dictionary (got it in yaml)