Transform rows to columns with condition in Qliksense script

[Nikhil2725]

I have records like below in TestResults table. In here every lender_id has 3 attempts to complete the finaltest, where he has to score 60% and above to pass the finaltest.

 lender_id  test_results percentage_score date_created      test_type

23421         2/10                   20                                       2018-01-10      finalTest

23421         4/10                    40                                      2018-01-11       finalTest

23421         6/10                    60                                       2018-01-12        finalTest

23444         4/10                    40                                       2018-01-10          finalTest

65432         9/10                    90                                        2018-01-21          finalTest

23455          4/10                   40                                      2018-01-11           finalTest

23455          3/10                  30                                        2018-01-03         finalTest

 i want to display as in below for each lender_id in order of date_created

lender_id  first_attempt  second_attempt  third_attempt

23421              20                                40                         60

65432               90

23455              30                                40

23444               40

How i could do these. Let me know if you need more info.

[kaanerisen]

Hi Nikhil2725,

How about this?

tempTable:
load
lender_id,
test_results,
percentage_score,
date(date#(date_created,'YYYY-MM-DD')) as date_created,
test_type
Inline [
lender_id,test_results,percentage_score,date_created,test_type
23421,'2/10',20,2018-01-10,'finalTest'
23421,'4/10',40,2018-01-11,'finalTest'
23421,'6/10',60,2018-01-12,'finalTest'
23444,'4/10',40,2018-01-10,'finalTest'
65432,'9/10',90,2018-01-21,'finalTest'
23455,'4/10',40,2018-01-11,'finalTest'
23455,'3/10',30, 2018-01-03,'finalTest'
];

NoConcatenate

Records:
load *,pick(match(AutoNumber(date_created,lender_id),1,2,3),'First','Second','Third') as examNo
Resident tempTable
order by lender_id,date_created asc
;

drop Tables tempTable;

NoConcatenate

Master:
LOAD Distinct lender_id
Resident Records;


Temp_columnList:
load Distinct examNo as column
Resident Records;

for Each a in FieldValueList('column')
left Join(Master)
load lender_id,
percentage_score as '$(a)_Exam'
Resident Records
where examNo='$(a)';

next;

drop tables Temp_columnList;

Hope it helps.

[Nikhil2725]

Hi tried your example, however with INLINE date the results are getting. But when i tried using the DB table the same results are not displaying.

I.e In master table i'm not getting First_exam, Second_exam, Third_exam.

Please find the attached QVF file

[Nikhil2725]

Please find attached qvf file

[kaanerisen]

Hi,

@Nikhil2725 wrote:

Hi tried your example, however with INLINE date the results are getting. But when i tried using the DB table the same results are not displaying.

---

The attached qvf's output seems exactly the same to me. I am confused little bit 🙂 What is the problem about that? 

[Nikhil2725]

sorry, my bad. I attached different records file.

Can you please refer this?. In this i am not able to add first_exam, second_exam and third exam fields

[kaanerisen]

"date_created" field is the reason why it is not working. This field is null on memory.

date pattern of the field on DB should be different from 'YYYY-MM-DD'. you should change the pattern on script with the pattern it should be. Then it will work.

date(date#(date_created,'YYYY-MM-DD')) as date_created

[santhiqlik]

Hi,

I appreciate that you had provided a solution. And I would like to suggest the below simplified approach of the solution you have provided.  Instead of creating 2 tables, I used Concat & Subfield in the same table. This is just a suggesstion. 

tempTable:
load
lender_id,
test_results,
percentage_score,
date(date#(date_created,'YYYY-MM-DD')) as date_created,
test_type
Inline [
lender_id,test_results,percentage_score,date_created,test_type
23421,'2/10',20,2018-01-10,'finalTest'
23421,'4/10',40,2018-01-11,'finalTest'
23421,'6/10',60,2018-01-12,'finalTest'
23444,'4/10',40,2018-01-10,'finalTest'
65432,'9/10',90,2018-01-21,'finalTest'
23455,'4/10',40,2018-01-11,'finalTest'
23455,'3/10',30, 2018-01-03,'finalTest'
];

mainTable:
Load
lender_id,
Subfield(PercentData,';',1) as first_attempt,
Subfield(PercentData,';',2) as second_attempt,
Subfield(PercentData,';',3) as third_attempt
;
Load
lender_id,
Concat(percentage_score,';',date_created) as PercentData //third parameter of concat will sort the values based on i/p
Resident tempTable Group by lender_id ;

Drop table tempTable;