Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have more than one lender_offer_id for single User_id. So i need to get latest lender_offer_id of that particular user_id. and
I am trying to execute below code, but giving me Error: Invalid Expression
OLD:
LOAD `user_id`,
`batch_meta_data_id`,
`lender_offer_id`,
`course_completed`,
`date_completed`,
`date_created` as Course_Date;
SQL SELECT `user_id`,
`batch_meta_data_id`,
`lender_offer_id`,
`course_completed`,
`date_completed`,
`date_created` // date created lender_offer
FROM dfgt.COURSECOMPLETION;
New:
LOAD `user_id` as new_User,
`batch_meta_data_id` as New_batch,
firstsortedvalue(lender_offer_id,-lender_offer_id) as New_lender,
`course_completed` as New_Course,
`date_completed` as New_Date_completed,
`Course_Date` as New_Course_Date
resident OLD
Group by lender_offer_id;
drop table OLD;
any help is appreciated.
Hi Supriya,
Firstsorted value acts similar to Aggregate functions, so every field which is outside of firstsorted value must go inside the group by clause.
Based on your requirement try below steps
New:
Load
user_id,
firstsortedvalue(lender_offer_id,-lender_offer_id) as Newlender
Resident Old
group by user_id;
Later add other fields as required and then drop the table,
Try to use this and see if it works..
Hi
You need to group by all fields and not just lender_offer_id