Transformation of Data

[Nikhil2725]

Hi All,

I have data listed below for the reference.

Lender_id	Question_text	Answer_text
1	How are you	Fine!
1	How old are you	21
1	What is your name	ABC
2	Which is your fav color	Red
2	Which is your Sport	Cricket
2	Which is fav dish	Non-Veg

Further, I want to display as below: Kindly help me in doing this..

Lender_id	Q1	A1	Q2	A2	Q3	A3
1	How are you	Fine!	How old are you	21	What is your name	ABC
2	Which is your fav color	Red	Which is your Sport	Cricket	Which is fav dish	Non-Veg

[Channa]

i am not sure but try

add ROWNUM

rank (ROWNUM) for each Lender_id

lender_id --RANK

1--1

1--2

1--3

you will make 3 sets where rank=1,rank=2,rank=3

[sunny_talwar]

Each lender_id can have a maximum of 3 questions only (or a finite set of questions)? Also, are you looking to do this in the script or front end app?

[mdmukramali]

Dear,

Below is the script to get the expected output.

Table:
LOAD *,
If(Lender_id= Previous(Lender_id),
RangeSum(Peek('QuestionNum'), 1), 1) as QuestionNum,
;
LOAD * Inline
[
Lender_id,Question_text,Answer_text
1,How are you,Fine!
1,How old are you,21
1,What is your name,ABC
2,Which is your fav color,Red
2,Which is your Sport,Cricket
2,Which is fav dish,Non-Veg
];

FinalTable:
LOAD Distinct Lender_id
Resident Table;

FOR i = 1 to FieldValueCount('QuestionNum')

LET vField = FieldValue('QuestionNum', $(i));

Left Join (FinalTable)
LOAD Lender_id,
Question_text as [Q$(vField)],
Answer_text as [A$(vField)]
Resident Table
Where QuestionNum = $(vField);

NEXT i;

DROP Table Table;

Attached the sample application also.

Thanks,

Mohammed Mukram

[Nikhil2725]

Hi Mohammed Mukram,
Thanks for the reply.
Without using Inline function to load data.
How can I use my DB to store the data?
I have a Questions table, I have the following fields and need to insert those data from the question table.
*question_id
*lender_id
*question_text
*answer_text
*answer_id
To retrieve the data...

[mdmukramali]

Hi Nikhil,

Can you share the sample data in the excel file?

it will be easy for us to help you.

Thanks,
Mohammed Mukram

[Nikhil2725]

Hi,
Can I known why you have used RangeSum(Peek('QuestionNum'), 1), 1) as QuestionNum...
And also if Im not wrong there is no field name called QuestionNum

[Nikhil2725]

As I am using Qlik sense Excel file will not be available...
Kindly help me in loading the file through DB

[Nikhil2725]

Hi Sunny_talwar ,
Each lender_id can have maximum of 10 question and im trying to do in script.

Thanks

[sunny_talwar]

Replace this portion with your database query