Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmeplease
Creator III
Creator III

Set analysis in Script equivalent

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,

6 Replies
adamdavi3s
Master
Master

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;

qlikmeplease
Creator III
Creator III
Author

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
Master III
Master III

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

ElizaF
Creator II
Creator II

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;



qlikmeplease
Creator III
Creator III
Author

Hi,

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

Regards,

rahulpawarb
Specialist III
Specialist III

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