topic Re: Set analysis in Script equivalent in QlikView

Set analysis in Script equivalent

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Set analysis in Script equivalent

adamdavi3s — Fri, 24 Mar 2017 10:28:53 GMT

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;

Re: Set analysis in Script equivalent

Anonymous — Fri, 24 Mar 2017 10:38:01 GMT

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,

Re: Set analysis in Script equivalent

sasiparupudi1 — Sat, 25 Mar 2017 21:31:49 GMT

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

Re: Set analysis in Script equivalent

ElizaF — Sun, 26 Mar 2017 09:21:03 GMT

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;

Re: Set analysis in Script equivalent

Anonymous — Wed, 29 Mar 2017 05:05:51 GMT

Hi,

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

Regards,

Re: Set analysis in Script equivalent

rahulpawarb — Wed, 29 Mar 2017 05:50:36 GMT

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