Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
guillaumebi
Contributor II
Contributor II

Calculated Field : max date Y/N

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 NameTeamDateMaxDate
Paris420HR10/05/2018yes
Paris420Sales10/05/2018yes
Paris420HR06/03/2018no
Paris420Sales06/03/2018no
Singapore387HR06/09/2017no
Singapore387Sales06/09/2017no
Singapore387HR12/05/2018yes
Singapore387Sales12/05/2018yes
Singapore387HR06/03/2018no
Singapore387Sales06/03/2018no
HK73HR15/04/2018yes
HK73Sales15/04/2018yes
HK73HR07/04/2018no
HK73Sales07/04/2018no

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 !

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

4 Replies
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;

guillaumebi
Contributor II
Contributor II
Author

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

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;

Capture.PNG

View solution in original post

guillaumebi
Contributor II
Contributor II
Author

Thank you, it is well working !