Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Have a look at
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
Maybe something like
CROSSTABLE (Question, Answer, 3)
LOAD * FROM ....;