topic Re: Calculated Field : max date Y/N in QlikView

Calculated Field : max date Y/N

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

Hi everyone,

I started recently to use QlikView so I have some issues to adapt myself.

I would like to know how to create a field in the script which tell me if the line checked has the maximum date depending on a criterion.

I don't want a chart solution, my aim is to do a data treatment to extract an excel after.

Example:

Data:

Assessment Name	Team	Date	MaxDate
Paris420	HR	10/05/2018	yes
Paris420	Sales	10/05/2018	yes
Paris420	HR	06/03/2018	no
Paris420	Sales	06/03/2018	no
Singapore387	HR	06/09/2017	no
Singapore387	Sales	06/09/2017	no
Singapore387	HR	12/05/2018	yes
Singapore387	Sales	12/05/2018	yes
Singapore387	HR	06/03/2018	no
Singapore387	Sales	06/03/2018	no
HK73	HR	15/04/2018	yes
HK73	Sales	15/04/2018	yes
HK73	HR	07/04/2018	no
HK73	Sales	07/04/2018	no

The column MaxDate is what I'm expecting :

If the date is the maximum for an Assessment Name, it returns "Yes", else "no".

Thank you for your help !

Re: Calculated Field : max date Y/N

sunny_talwar — Thu, 21 Jun 2018 14:51:05 GMT

May be this

Data:

LOAD [Assessment Name],

Team,

Date

FROM ...;

Left Join (Data)

LOAD [Assessment Name],

Max(Date) as Date',

'Yes' as MaxDate

Resident Data;

FinalData:

NoConcatenate

LOAD [Assessment Name],

Team,

Date,

If(Len(Trim(MaxDate)) = 0, 'no', MaxDate) as MaxDate

Resident Data;

DROP Table Data;

Re: Calculated Field : max date Y/N

Anonymous — Thu, 21 Jun 2018 15:51:47 GMT

Hello,

Thanks for your help but it returns me an error :

I saw that there is a function called firstsortedvalue() could it be used for this usecase (to generate a new calculated field in the script)?

Thanks to you, community

Re: Calculated Field : max date Y/N

sunny_talwar — Thu, 21 Jun 2018 17:08:04 GMT

My bad, try this

Data:

LOAD * INLINE [

Assessment Name, Team, Date

Paris420, HR, 10/05/2018

Paris420, Sales, 10/05/2018

Paris420, HR, 06/03/2018

Paris420, Sales, 06/03/2018

Singapore387, HR, 06/09/2017

Singapore387, Sales, 06/09/2017

Singapore387, HR, 12/05/2018

Singapore387, Sales, 12/05/2018

Singapore387, HR, 06/03/2018

Singapore387, Sales, 06/03/2018

HK73, HR, 15/04/2018

HK73, Sales, 15/04/2018

HK73, HR, 07/04/2018

HK73, Sales, 07/04/2018

];

Left Join (Data)

LOAD [Assessment Name],

Max(Date) as Date,

'Yes' as MaxDate

Resident Data

Group By [Assessment Name];

FinalData:

NoConcatenate

LOAD [Assessment Name],

Team,

Date,

If(Len(Trim(MaxDate)) = 0, 'no', MaxDate) as MaxDate

Resident Data;

DROP Table Data;

Re: Calculated Field : max date Y/N

Anonymous — Fri, 22 Jun 2018 09:26:14 GMT

Thank you, it is well working !

Re: Calculated Field : max date Y/N

panosalexand — Tue, 11 Apr 2023 09:44:19 GMT

Thanks Sunny for the idea. It's also worked for me. I just put my example here. It might be useful for someone else😃

TM_A:
QUALIFY *;
UNQUALIFY ApplicationId,[GroupId],ApplicationDateMin;

LOAD *

FROM table A;

Left join(TM_A)

Load
GroupId,

if(Len(Trim(GroupId))>0, Min(Date(TM_REQ.ApplicationDate, 'DD/MM/YYYY'))) as ApplicationDateMin

Resident TM_A
group by GroupId;

FinalData:

NoConcatenate

LOAD GroupId,

if(Len(Trim(GroupId))=0, Date(TM_REQ.ApplicationDateNew, 'DD/MM/YYYY'),ApplicationDateMin) as Application_CalendarDate

Resident TM_A;

Regards,
Panos