topic Re: getting latest record according to date from table in QlikView

getting latest record according to date from table

berryandcherry6 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i have a table with multiple lender_id for single user_id.

lender_id user_id date_created

43	1	12/19/2016
44	1	12/20/2016
45	1	12/21/2016
46	2	12/19/2016
47	2	12/25/2016
48	2	12/23/2016
49	2	12/20/2016
50	3	12/20/2016
51	3	12/21/2016
52	1	12/25/2016
53	1	12/21/2016
54	4	12/19/2016

Now i need to put query to get as this

lender_id user_id date_created

47	2	12/25/2016
51	3	12/21/2016
52	1	12/25/2016
54	4	12/19/2016

How could i do this. Please help me.

Re: getting latest record according to date from table

sunny_talwar — Thu, 29 Dec 2016 11:57:03 GMT

Did you try this?

Re: Error: Function WeekStart Doesnot Exist. In Where clause

Re: getting latest record according to date from table

Anonymous — Thu, 29 Dec 2016 11:58:24 GMT

Hi,

Data:

LOAD * inline [

lender_id, user_id, date_created,

43, 1, 12/19/2016

44, 1, 12/20/2016

45, 1, 12/21/2016

46, 2, 12/19/2016

47, 2, 12/25/2016

48, 2, 12/23/2016

49, 2, 12/20/2016

50, 3, 12/20/2016

51, 3, 12/21/2016

52, 1, 12/25/2016

53, 1, 12/21/2016

54, 4, 12/19/2016 ];

NoConcatenate

Data_TEMP:

LOAD lender_id,

user_id,

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

Resident Data;

drop table Data;

Data_Max:

load user_id, max(date_created) as date_created

Resident Data_TEMP

Group by user_id ;

left join(Data_Max)

LOAD lender_id,

user_id,

date_created

Resident Data_TEMP;

drop table Data_TEMP;

Best regards,

Cosmina