LOAD MAX DISTINCT ? DATE

[Anonymous]

Hi everybody, hope somebody can help me. I have a table with the following structure

For the same position I have several Items movements and the quantity of the items. What I try to achieve is this table

The one which contains the Qty per position and with the highest date. Do I have to do this while the scripts loads or can I do it later in a graphic with some kind of aggregation?

Thanks in advances

[jyothish8807]

Hi Pitutiti,

Create two variables.

Var1=max(Date)

Var2=min(Date)

Create a straight chart.

In expression:

if(Date=Variable1 or Date=Variable2,Quantity)

Dimension: Date

Then in presentation tab:

tick suppress zero value.

Regards

KC

[#3]

Hi,

Take Position as Dimension

And in Exp1:

Only({<Date=  {"=$(=Max([Date]))"}>}Date)

Exp2:

Only({<Date=  {"=$(=Max([Date]))"}>}Qty)

Hope this will Work.

Regards,

Joshmi

[rubenmarin]

Hi pitutiti,you can use this expression in chart:

Sum(Aggr(If(Date=Max(Total <Position> Date), Sum(Qty)), Position, Date))

For a input data like this:

Date, Position, Qty

01/01/2015, 1, 200

01/02/2015, 1, 400

06/01/2015, 2, 100

05/02/2015, 2, 300

will return:

[Anonymous]

Hi KC, It doesn't work for me because I don't want to get the maximal date from the entire table, I want to get the maximal date of the small groups of rows where the field position have the same value.

But now that I'm reviewing what I wrote I realize I made a mistake with the rows selected.

The correct table is this one.

Sorry for the mistake and thanks for your time.

[Anonymous]

Thanks Ruben, but i want the single row, not the sum.

[rubenmarin · Accepted solution]

If its only one row per max(Date) and position it will get only that row, try the expression and see if it works.

You can see that each row it's showing data according to the input data.

[maxgro]

RESULT

SCRIPT

order by position and date desc

get only first position (max date by position)

a:

load * inline [

Date, Position, Qty

01/02/2015, 1, 100

01/01/2015, 1, 200

05/02/2015, 1, 300

04/12/2014, 2, 150

06/01/2015, 2, 80

03/02/2015, 2, 50

01/12/2014, 3, 75,

15/01/2015, 3, 80

04/02/2015, 3, 90

];

final:

NoConcatenate load

  *

Resident a

Where peek(Position)<>Position

order by Position, Date desc;

DROP Table a;

[Anonymous]

Yeah, you were right! Thanks a lot and sorry for the delay in my response.