Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smiling_cheetah
Creator
Creator

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

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 1Header 2Header 3Header 4Header 5
Value 1Value 2Value 3Value 4And so on...

And want to transform it into a new one

HeadersValues
Header 1Value 1
Header 2Value 2
Header 3Value 3
Header 4Value 4

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

Thanks

1 Solution
4 Replies
smiling_cheetah
Creator
Creator
Author

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;

Anil_Babu_Samineni

Use Cross Table while transpose the Data

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
smiling_cheetah
Creator
Creator
Author

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)