topic Re: LOAD MAX DISTINCT ? DATE in QlikView

LOAD MAX DISTINCT ? DATE

Anonymous — Thu, 05 Feb 2015 11:43:24 GMT

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

Re: LOAD MAX DISTINCT ? DATE

jyothish8807 — Thu, 05 Feb 2015 11:53:08 GMT

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

Re: LOAD MAX DISTINCT ? DATE

Thu, 05 Feb 2015 12:10:48 GMT

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

Re: LOAD MAX DISTINCT ? DATE

rubenmarin — Thu, 05 Feb 2015 12:14:48 GMT

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:

Re: LOAD MAX DISTINCT ? DATE

Anonymous — Thu, 05 Feb 2015 12:36:11 GMT

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.

Re: LOAD MAX DISTINCT ? DATE

Anonymous — Thu, 05 Feb 2015 12:41:15 GMT

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

Re: LOAD MAX DISTINCT ? DATE

rubenmarin — Thu, 05 Feb 2015 12:45:09 GMT

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.

Re: LOAD MAX DISTINCT ? DATE

maxgro — Thu, 05 Feb 2015 12:52:15 GMT

RESULT

SCRIPT

order by position and date desc

get only first position (max date by position)

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;

Re: LOAD MAX DISTINCT ? DATE

Anonymous — Mon, 09 Feb 2015 14:29:24 GMT

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