Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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
Highlighted

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
Highlighted

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

Highlighted

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

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)

Highlighted

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

Highlighted
Contributor
Contributor

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.

Highlighted

May be this

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