Set analysis in Script equivalent

[Anonymous]

Hi,

So my data looks like the below.

Article     Time       Field I want below

1234      12:03:02

1234      12:05:00

1234      13:00:02

1235      13:01:02

1235      13:02:02

1235      13:56:00

I need to create the equivalent of an array formula in excel (which led me to set analysis). Which returns the MAX time for a particular Article.

I am not sure how to write the set analysis or whether it is actually possible.

thus : MAX(IF(Article=Article,Time)  (just a sample of the idea)

In more detail: I need the formula to evaluate each Article in the range to see if it matches the row Article and return the MAX Time.

Thanks in Advance ! Also, if not possible in the script, a front face solution will also be appreciated.

Regards,

[adamdavi3s]

Ok so I would create a new table and join it back, hard to advise without table names etc but I can give you the basic syntax to use:

noconcatentate

tmp:

LOAD Article, Max(Time) as NewField

resident yourtable

group by Article;

LEFT JOIN (yourtable)

LOAD * resident tmp;

drop table tmp;

[Anonymous]

Hi ,

I also had this solution, but the joining didnt work properly, I realised this was a result of bad formatting.

Thanks for the solution !

Regards,

[sasiparupudi1]

Pl close this issue by marking the correct answer if you like the solution given.

[ElizaF]

Hi,

Another solution is applymap function.

Max_Map:

Mapping

LOAD

Article,

Date(Max(Time),' hh:mm:ss')  as MaxTime

from yourtable

group by Article;

Table:

Load

Article,

Time,

Applymap('Max_Map',Article,0) as MaxTime

from yourtable;

[Anonymous]

Hi,

I did mark the answer as helpful, is there another way to mark it as correct?

Regards,

[rahulpawarb]

Hello Ruan,

Trust that you are doing great!

I agree with solutions of Adam & Eliza. BTW, as a front-end workaround you can add a calculated dimension with below definition.

=Aggr(MaxString(Time), Article)

Also refer the sample application attached herewith.

Hope this will be helpful.

Regards!

Rahul