topic Re: Max Start Date in QlikView

Max Start Date

tmumaw — Tue, 17 Jan 2017 12:39:57 GMT

Good morning everyone,

Simple question for you all. I need to get the max start date from this sample group of employees. What is the easy way to do it? I have already converted the start date to a numeric field. I only want to see the numeric start date equal to 42734.

Thanks

Re: Max Start Date

sunny_talwar — Tue, 17 Jan 2017 12:47:37 GMT

May be just this:

Date(Max([Start Date]))

Re: Max Start Date

tmumaw — Tue, 17 Jan 2017 12:57:23 GMT

Where would you use this? To only get one record don't you have to do a group by?

Temp:
LOAD [End Date],
[Start Date],
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Week Ending],
LinkKey,
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
(qvd)
Where Employment_STAT2 = '0' or Employment_STAT2 = '1' or Employment_STAT2 = '2' ;

Re: Max Start Date

sunny_talwar — Tue, 17 Jan 2017 12:59:40 GMT

In the script, may be try this:

Temp:
LOAD [End Date],
[Start Date],
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Week Ending],
LinkKey,
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
(qvd)
Where Employment_STAT2 = '0' or Employment_STAT2 = '1' or Employment_STAT2 = '2' ;

Right Join (Temp)

LOAD [Employee Number],

Max([Start Date]) as [Start Date]

Resident Temp

Group By [Employee Number];

Re: Max Start Date

rahulpawarb — Tue, 17 Jan 2017 13:02:43 GMT

Hello Thom,

Trust you are doing good!

I agree with Sunny. However, you can refer below script to solve your concern.

Data:
LOAD * INLINE [
EmployeeNo, StartDate
0111, 40250
0111, 40251
0111, 40260
0111, 40150
];
INNER JOIN 
LOAD EmployeeNo,
     MAX(StartDate) AS NumStartDate
Resident Data
GROUP BY EmployeeNo;

Hope this will be helpful.

Regards!

Rahul

Re: Max Start Date

sunny_talwar — Tue, 17 Jan 2017 13:06:10 GMT

This is not going to work because you renamed startdate to numstartdate. The inner join will occur only on EmployeeNo keeping all the 4 rows intact

Re: Max Start Date

tmumaw — Tue, 17 Jan 2017 13:06:49 GMT

Thanks Sunny T. Worked perfect.

Re: Max Start Date

rahulpawarb — Tue, 17 Jan 2017 16:15:10 GMT

Hello Sunny,

My sincere apologies. I misunderstood the requirement. You are correct.

Regards!

Rahul

Re: Max Start Date

sunny_talwar — Tue, 17 Jan 2017 16:17:49 GMT

You don't have apologize brother. I was just pointing out that in order for your solution to work, you would just need to keep the name of the field you are joining on the same as it is in the above table

Data:
LOAD * INLINE [
EmployeeNo, StartDate
0111, 40250
0111, 40251
0111, 40260
0111, 40150
];
INNER JOIN
LOAD EmployeeNo,
MAX(StartDate) AS StartDate
Resident Data
GROUP BY EmployeeNo;

This is all you needed