Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
we have option in qlik sense dataload convert to pivot try
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.
first join these two table based on QuestionId and load into QVD later you use pivot
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)
join those 2 tables no need piviot
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.
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