Calculated Field : max date Y/N

[Anonymous]

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 !

[sunny_talwar]

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;

[Anonymous]

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

[sunny_talwar · Accepted solution]

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;

[Anonymous]

Thank you, it is well working !

[panosalexand]

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