Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
smiling_cheetah
Contributor

[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

Tags (1)
1 Solution

Accepted Solutions
el_aprendiz111
Valued Contributor

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

4 Replies
smiling_cheetah
Contributor

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

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
Valued Contributor

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

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

Use Cross Table while transpose the Data

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
Contributor

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

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)