extracting values as column names

Tue, 24 Jan 2012 10:01:33 GMT

Hello,

I have a lack of insight on the following topic. I would appriciate any assistance a lot.

I have a table that has "id" and "question_name" columns. (e.g. Question_name values are "Q1","Q2","Q3"...)

I have another table which has "user" column, and the scores for each questions as columns(column Q1, column Q2, column Q3).

I want to create a straight table, which shows the min, avg and the max values for each question.

Example table i want to create:

min avg max

Q1 6 7 8

Q2 5 6 10

Q3 12 13 16

I couldn't figure out how to relate the name of the questions of the first table to the second table's column names.

Thanks in advance.

vijay_iitkgp — Tue, 24 Jan 2012 10:13:35 GMT

Hi,

I could not find any relation between table 1 and table2. However you can achieve your desired value by using table2 only.

If your structure for table2 is:

User Q1 Q2 Q3 Q4 Q5

1 4 6 12 8 9

2 8 9 7 12 5

.....

...

Then you can create cross table:

Tab1:
LOAD * INLINE [
    User, Q1, Q2
    1, 6, 5
    2, 8, 3
    3, 9, 3
];

Tab2:
CrossTable(Question, Value)
LOAD *
Resident Tab1;

Drop Table Tab1;

Hope this will help

topic extracting values as column names in QlikView