Max date par mois

[Anonymous]

Bonjoir,

HELP! Je voudrais selectionner les enregistrements du dernier jour de chaque MoisAnnée dans le script load.

Exemple:

date	enregistrement
01/08/2012	text1
06/08/2012	text2
01/09/2012	text3

le resultat doit etre :

date	enregistrement
06/08:2012	text2
01/09/2012	text3

Merci d'avance

[robert99]

Thats what I thought

But what did you get?

[Anonymous]

I get the date

31/08/2012    text1

31/08/2012   text 2

.......

but the result that i need is to keep the same date as the file and pick the last one for every month

[robert99]

did you load

step1

data:

Date,

enregistrement,

Monthend (date) as monthend

from datatable;

to begin with

because if so I can not see how date changed to 31/8/2012 (this is the monthend date

[Anonymous]

The result that i get is:

Date                           Enregistrement                    MonthENd                         maxdateformonth

01/08/2012                    text1                                          31/08/2012                31/08/2012

08/08/2012                    Text2(anything)                         31/08/2012               31/08/2012

the table in the result stil contain the 2 dates

i need just the second line !

Thanks

[robert99]

Unsure where you have got this table from

But DON'T include MonthEnd as a dimension

In fact you could add a drop statement in your script (at the end)

drop field Monthend;

I'm off out but can you post your script if it still doesn't work

re the 1st line

This shoudl get rid of it

where (maxdateformonth = date);

But you need to post your script

[Anonymous]

it was a simple table just to see the result

and the max ( date) is return all the time 31/Month/Year or 30/Month/Year

i don't need this

just the simple result i need to get in my finale table ( data2 in your exemple)

date      enregistrement

08/08/2012      'whatever'

09/09/2012     'whatever'

so i should get the max of the day in EVERY month and EVERY year

i think that we is a  missunderstinding !

Thanks for your help

My script:

data:

Load

     date

    Monthend(Date) As Month_Flux

Resident table1;

join

load Month_Flux,

          max(Date) as MaxDate

resident data;

Final:

Load *

resident data

where MaxDate=Date;

[robert99]

Your script would not work. and its NOT what I wrote above

My script:

data:

Load

enregistrement,

     date,

    Monthend(Date) As Month_Flux

Resident???????  from table1;

join

load Month_Flux,

          max(Date) as MaxDate

resident data

GROUP BY Month_Flux

;

Final:

NOCONCATENATE

Load *

resident data

where MaxDate=Date;

DROP TABLE DATA;

DROP FIELD Month_Flux;

[Anonymous · Accepted solution]

Thank you !
but it not working !

this solution work :

http://www.qlikcommunity.com/thread/47479

[anbu1984]

Load Customer,FirstSortedValue(Turnover,-Date),FirstSortedValue(Date,-Date) Group by Customer;

Load Customer, Turnover, Date#(Date,'DD.MM.YYYY') As Date Resident Temp_Customer;