Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ];
Hey,
Thanks for the reply...
But can I Use DB values instead on INLINE???
If possible, how can I Do that?
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 ....;
Super, please close the thread in that case 🙂