AGGR in Script (requires minimum?)

[NewbieToQV]

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!

[sunny_talwar · Accepted solution]

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];

[sunny_talwar]

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];

[NewbieToQV]

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)

[sunny_talwar]

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

[NewbieToQV]

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.

[sunny_talwar]

May be this

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