Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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;
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,
Pl close this issue by marking the correct answer if you like the solution given.
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;
Hi,
I did mark the answer as helpful, is there another way to mark it as correct?
Regards,
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