topic How to convert the data format in QlikView

How to convert the data format

Wed, 25 Nov 2020 16:16:04 GMT

I try to convert the data into a different structure when loading the data. Here is the old data format I have:

[raw data]

Customer ID	Category	Brand	Image1	Image2	Rank	Copy
111	Milk	abc	Strongly Disagree	Agree	J2	Neutral
112	Milk	abc	Neutral	Disagree	J3	Strongly Disagree
113	Milk	abc	Disagree	Neutral	J2	Neutral
114	Milk	abc	Strongly Agree	Strongly Disagree	J4	Neutral

The expected data structure would be:

Customer ID	Category	Brand	Question	Answer
111	Milk	abc	Image1	Strongly Disagree
112	Milk	abc	Image1	Neutral
113	Milk	abc	Image1	Disagree
114	Milk	abc	Image1	Strongly Agree
111	Milk	abc	Image2	Agree
112	Milk	abc	Image2	Disagree
113	Milk	abc	Image2	Neutral
114	Milk	abc	Image2	Strongly Disagree
111	Milk	abc	Rank	J2
112	Milk	abc	Rank	J3
113	Milk	abc	Rank	J2
114	Milk	abc	Rank	J4
111	Milk	abc	Copy	Neutral
112	Milk	abc	Copy	Strongly Disagree
113	Milk	abc	Copy	Neutral
114	Milk	abc	Copy	Neutral

Since # of column is flexible, so I use for.. next to load. it should be something like:

For i = 1 to NoOfFields([raw data])-4

Concatenate

[temp]:

LOAD

[Customer ID] as CID,

Category as Category,

Brand as Brand,

FieldName(i+4,[raw data]) as Question;

Next i

But this does not work. Can someone tells me what's wrong with this code? I am new to Qlikview, so not sure if my code is correct or not in Qlikview.

Re: How to convert the data format

swuehl — Fri, 25 Aug 2017 21:23:43 GMT

Have a look at

The Crosstable Load

Re: How to convert the data format

Fri, 25 Aug 2017 21:28:28 GMT

Thanks for sharing this. It's helpful for a given table.

But if I have undefined # of fields and the field name is also different for each table. Is there a way to create a loop to crosstable dynamically.

For example, I will do 3 fields in this table, but in next table I need to do 9 fields. If I can have a code and do not need to change for each table when doing this data transfer.

Thanks

Re: How to convert the data format

swuehl — Fri, 25 Aug 2017 21:36:58 GMT

Maybe something like

CROSSTABLE (Question, Answer, 3)

LOAD * FROM ....;