Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
NewbieToQV
Contributor II
Contributor II

AGGR in Script (requires minimum?)

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 NameType (Rank)DateOutcome (from script)Outcome for Count
A11 Feb 2020A1
A21 Jan 2020blank0
A21 Jan 2020blank0
B31 Apr 2020B0
B31 Mar 2020B1

 

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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))