Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Select Highest Day of Month

Hello,

I have a collection of records with dates. I want to select the highest day of each month. for example:

Article1 - 01-08-2013

Article1 - 10-08-2013

Article2 - 05-08-2013

Article2 - 14-08-2013

I want to select:

Article1 - 10-08-2013

Article2 - 14-08-2013

I want to do this in script. Something like MaxDay(), but MaxDate from the specific month.

1 Solution

Accepted Solutions
MVP
MVP

Re: Select Highest Day of Month

add a

     ...

     FirstSortedValue(SalesValue, -Date) as SalesValueAtMaxDate,

     ...

8 Replies
MVP
MVP

Re: Select Highest Day of Month

Maybe like this

Table1:

LOAD Article, Date FROM ...;

Result:

Load Article,

        Date(max(Date) as MaxDate

Resident Table1

Group By Article, monthstart(Date);

Not applicable

Re: Select Highest Day of Month

This does not work for me. I get a script error.

MVP
MVP

Re: Select Highest Day of Month

Seems that I missed a closing bracket for the Date() function.

After correcting this, works ok for me:

SET DateFormat = 'DD-MM-YYYY';

INPUT:

LOAD * INLINE [

Article, Date

Article1,01-08-2013

Article1,10-08-2013

Article2,05-08-2013

Article2,14-08-2013

Article1,01-07-2013

Article1,15-07-2013

Article2,05-07-2013

Article2,06-07-2013

];

RESULT:

Load Article,

        Date(max(Date)) as MaxDate

Resident INPUT

Group By Article, monthstart(Date);

er_mohit
Honored Contributor II

Re: Select Highest Day of Month

Copy and paste this script

Final:

LOAD Article,

Date(max(Date),'DD-MM-YYYY')as maxDate

Group by Article;

INPUT:

LOAD * INLINE [

Article, Date

Article1,01-08-2013

Article1,10-08-2013

Article2,05-08-2013

Article2,14-08-2013

Article1,01-07-2013

Article1,15-07-2013

Article2,05-07-2013

Article2,06-07-2013

];

Not applicable

Re: Select Highest Day of Month

SEE ATTACHED FILE

Not applicable

Re: Select Highest Day of Month

Thank you. Now this works. However, I have more columns connected to these articles. Lets say:

Article1 - 01-08-2013 - €10

Article1 - 10-08-2013 - €12

Article2 - 05-08-2013 - € 13

Article2 - 14-08-2013 - € 9

I want to select:

Article1 - 10-08-2013 - € 12

Article2 - 14-08-2013 - € 9

How can I put this in the provided code? The group by code seem to be troubling

MVP
MVP

Re: Select Highest Day of Month

add a

     ...

     FirstSortedValue(SalesValue, -Date) as SalesValueAtMaxDate,

     ...

rustyfishbones
Honored Contributor II

Re: Select Highest Day of Month

try this

LOAD

LASTVALUE(Date) AS Date

FROM xyz.csv

GROUP BY Article, MONTHSTART(Date);

Community Browser