topic Re: AGGR in Script (requires minimum?) / Count only one across 3 fields in QlikView

AGGR in Script (requires minimum?)

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

Hi, I need to load the below into QV loading script but unable to convert into a script. I tried a few variations of Min/FirstSortedValue but did not work. Please help.

Aggr(NODISTINCT If([Type (Rank)] = Min(TOTAL <[Group Name]> [Type (Rank)]), [Group Name]), [Group Name], [Type (Rank)])

What I need is for the same group name, I only want to show the Group Name with the smaller Type (Rank)

e.g.

Group Name	Type (Rank)	Date	Outcome (from script)	Outcome for Count
A	1	1 Feb 2020	A	1
A	2	1 Jan 2020	blank	0
A	2	1 Jan 2020	blank	0
B	3	1 Apr 2020	B	0
B	3	1 Mar 2020	B	1

The reason for creating the script is to act as a step to produce the final outcome of count. I was unable to nest 2 aggr in my table.

Thks!

Re: AGGR in Script (requires minimum?) / Count only one across 3 fields

sunny_talwar — Tue, 22 Sep 2020 12:40:28 GMT

Do you need the count from the script also?, if not, try this

Table: LOAD *, RowNo() as RowNum; LOAD * INLINE [ Group Name, Type (Rank), Date A, 1, 1 Feb 2020 A, 2, 1 Jan 2020 A, 2, 1 Jan 2020 B, 3, 1 Apr 2020 B, 3, 1 Mar 2020 ]; Left Join (Table) LOAD [Group Name], Min([Type (Rank)]) as [Type (Rank)], [Group Name] as [Outcome] Resident Table Group By [Group Name];

Re: AGGR in Script (requires minimum?) / Count only one across 3 fields

sunny_talwar — Tue, 22 Sep 2020 12:43:56 GMT

And may be this for the Outcome for count field

Table: LOAD RowNo() as RowNum, [Group Name], [Type (Rank)], Date#(Date, 'D MMM YYYY') as Date; LOAD * INLINE [ Group Name, Type (Rank), Date A, 1, 1 Feb 2020 A, 2, 1 Jan 2020 A, 2, 1 Jan 2020 B, 3, 1 Apr 2020 B, 3, 1 Mar 2020 ]; Left Join (Table) LOAD [Group Name], Min([Type (Rank)]) as [Type (Rank)], [Group Name] as [Outcome] Resident Table Group By [Group Name]; Left Join (Table) LOAD [Group Name], [Outcome], Min(Date) as Date, 1 as [Outcome for Count] Resident Table Where Len(Trim(Outcome)) > 0 Group By [Group Name], [Outcome];

Re: AGGR in Script (requires minimum?) / Count only one across 3 fields

NewbieToQV — Tue, 22 Sep 2020 12:54:13 GMT

Works like magic. Thanks. I will use that as a workaround for now.

Would you happen to be able to solve the count as well? (Without the script but as a function (2 aggr?) in the tables)

Re: AGGR in Script (requires minimum?) / Count only one across 3 fields

sunny_talwar — Tue, 22 Sep 2020 13:04:52 GMT

So, you want to Count Outcome field only once? May be use Count(DISTINCT Outcome)?

Re: AGGR in Script (requires minimum?) / Count only one across 3 fields

NewbieToQV — Tue, 22 Sep 2020 13:11:01 GMT

I want to count outcome once based on the earliest date.

I can do the modify the aggr that I posted earlier with the new field "Outcome" but was thinking is there another way to do within the expression and not create any new fields.

Re: AGGR in Script (requires minimum?) / Count only one across 3 fields

sunny_talwar — Tue, 22 Sep 2020 13:49:22 GMT

May be this

Sum(Aggr(If(Min(TOTAL <[Group Name], Outcome> {<Outcome *= {"*"}>} Date) = Date, 1, 0), [Group Name], [Type (Rank)], Date, Outcome))