Transform of Rows into Column

[Nikhil2725]

Hi All,

I have a records like below in Question And Answer table. Here for every question asked the user will be answered and the same wil be stored in the Answer Table.

Question Table.

Lender_id, Question_id, Question_text,                 Question_num                                

1                              123         What is your name?                        1

1                              456         What is your Age?                           2                                                                                             

1                              789         What is your hobby?                      3

1                              111         Which is your fav Color?                4

Answer Table.

Question_id, Answer_text, Answer_num

123                         ABC                        1

456                         22                           2

789                         Playing                  3

111                         Red                        4

Now I want to display as below.

Question_num , Question_text, Answer_text,Question_num ,Question_text,Answer_text,Question_num,Question_text,Answer_text

1, What is your name?, ABC,2, What is your Age?, 22,3, What is your hobby?, Playing    

How i could do this?

[Channa]

we have option in qlik sense dataload convert to pivot try 

[Nikhil2725]

Hi,

Thanks for the Solution.

However, I am not only having 2 tables Question and Answer.

I am having more tables as well. Inserting Pivot table wont help me in this scenario.

If I am not wrong, pivot table requires measure field as well. Here in the above example, I cant give any field in measure field as well.

[Channa]

first join these two table based on QuestionId and load into QVD later you use pivot

[ryo_okabe]

Hi,

Why don't you to use concat function ?

Concat(distinct Question_num&','&Question_text&','&Answer_text,'',Question_num)

I attached sample qvf.

If you don't need  column lender_id, you should change chart Pivot to Table and change

dimension following and delete measure.

=Aggr(
Concat(distinct Question_num&','&Question_text&','&Answer_text,'',Question_num),
Lender_id)

[Channa]

join those 2 tables  no need piviot

[Nikhil2725]

Hi,
Thanks for the reply.

My Bad....

Data will be loaded as below:

Lender_id, Question_id, Question_text, Answer_text,
1 123 What is your name? ABC
1 456 What is your Age? 22
1 789 What is your hobby? Playing
1 111 Which is your fav Color? Red
However I want to display as below:
Lender_id question_text Answer_text question_text Answer_text,
1 What is your name? ABC What is your Age? 22

Like wise, I should get all the question_text and answer_text columns pertaining to particular lender_id

[Lincoln653]

If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative and absolute references before you rotate the data.

[Nikhil2725]

No, My data contains only text...

[Nikhil2725]

Data will be loaded as below:

Lender_id, Question_id, Question_text, Answer_text,
1 123 What is your name? ABC
1 456 What is your Age? 22
1 789 What is your hobby? Playing
1 111 Which is your fav Color? Red
However I want to display as below:
Lender_id question_text Answer_text question_text Answer_text,
1 What is your name? ABC What is your Age? 22

Like wise, I should get all the question_text and answer_text columns pertaining to particular lender_id