modify of records in table to display in qliksense

[Nikhil2725]

Hi All,

I have field called score data in that value will be stored as 2/5; 3/5; 4/5. From that score data i derive the first, second and third attempt values.

mainTable:
Load
lender_offer_id,
Subfield(scoreData,';',1) as first_attempt,
Subfield(scoreData,';',2) as second_attempt,
Subfield(scoreData,';',3) as third_attempt;

Load
lender_offer_id,
Concat(score,';',date_created) as scoreData //third parameter of concat will sort the values based on i/p
Resident tempTable Group by lender_offer_id ;
Drop table tempTable;

lender_offer_id,first_attempt,second_attempt,third_attempt

234,2/10,5/10,10/10

235,8/10

236,5/10,3/10,7/10

237,5/10,5/10,6/10

238,5/10,6/10

here, m/n where m is number of correct answers out of n questions and n is the total number of questions asked in test in each attempt

example,5/10 here 5 questions have been answered correctly out of 10 questions.

so now it will be 5 correct questions in first_attempt and 5 incorrect questions in first_attempt for lender_id 238.

i want to display like below

lender_offer_id,first_attempt_correct,first_attempt_incorrect,second_attempt_correct,second_attempt_incorrect,third_attempt_correct,third_attempt_incorrect

234,2,8,5,5,10,0

235,8,2

236,5,5,3,7,7,3

237,5,5,5,5,6,4

238,5,5,6,4

How could i retrieve this in my records as above. Please help me on this.

[sunny_talwar]

Try this

Table:
LOAD lender_offer_id,
	 If(Len(Trim(SubField(first_attempt, '/', 1))) > 0, SubField(first_attempt, '/', 1)) as first_attempt_correct,
	 SubField(first_attempt, '/', 2) - SubField(first_attempt, '/', 1) as first_attempt_incorrect,
	 If(Len(Trim(SubField(second_attempt, '/', 1))) > 0, SubField(second_attempt, '/', 1)) as second_attempt_correct,
	 SubField(second_attempt, '/', 2) - SubField(second_attempt, '/', 1) as second_attempt_incorrect,
	 If(Len(Trim(SubField(third_attempt, '/', 1))) > 0, SubField(third_attempt, '/', 1)) as third_attempt_correct,
	 SubField(third_attempt, '/', 2) - SubField(third_attempt, '/', 1) as third_attempt_incorrect;
LOAD * INLINE [
    lender_offer_id, first_attempt, second_attempt, third_attempt
    234, 2/10, 5/10, 10/10
    235, 8/10
    236, 5/10, 3/10, 7/10
    237, 5/10, 5/10, 6/10
    238, 5/10, 6/10
];

[Nikhil2725]

Hey,

Thanks for the reply...

But can I Use DB values instead on INLINE???

If possible, how can I Do that?

[sunny_talwar]

You sure can... all you need to do is to replace INLINE with your db query

Table:
LOAD lender_offer_id,
	 If(Len(Trim(SubField(first_attempt, '/', 1))) > 0, SubField(first_attempt, '/', 1)) as first_attempt_correct,
	 SubField(first_attempt, '/', 2) - SubField(first_attempt, '/', 1) as first_attempt_incorrect,
	 If(Len(Trim(SubField(second_attempt, '/', 1))) > 0, SubField(second_attempt, '/', 1)) as second_attempt_correct,
	 SubField(second_attempt, '/', 2) - SubField(second_attempt, '/', 1) as second_attempt_incorrect,
	 If(Len(Trim(SubField(third_attempt, '/', 1))) > 0, SubField(third_attempt, '/', 1)) as third_attempt_correct,
	 SubField(third_attempt, '/', 2) - SubField(third_attempt, '/', 1) as third_attempt_incorrect;
SQL SELECT
   lender_offer_id,
   first_attempt,
   second_attempt,
   third_attempt
FROM ....;

[Nikhil2725]

Hey,
Thanks for the reply. It worked..
Cheers....

[sunny_talwar]

Super, please close the thread in that case 🙂