Solved: Use AGGR in an dimension to return one result from... - Qlik Community

tinkerz1 · ‎2016-02-15

Hi,

I have an ID field that has a status field with 5 outcomes (Open,Accepted,Draft,Closed,'Blank')

The result I would like to return is just one status, the formula is to go through in order of the above list and

count the number of ID's per status.

If the count of the ID is zero move through the above list and stop when the count of the ID is not zero.

Then return that status. ie. Status='Draft'

example.

=if(aggr(count (ID),[STATUS]='Open')>0,
if([STATUS]='Open',[STATUS])
,[STATUS])

But this wont work if the row does not have an ID of Draft.

Thanks.

Report Inappropriate Content · ‎2016-02-15

Hi Neil,

You can acheive this by creating a flag table like below.

flag:

left Join(input)

load * inline [

Status,value

Open,1

Accepted,2

Draft,3

Closed,4

Blank,5

];

And you can join the input table with this flag table then you will get the ID, Status,value. Then in the next step we can take min(value) based on the group by ID. Then we will get the desired result.

Below is the script

input:

load * inline [

ID,Status

1,Open

1,Draft

2,Accepted

2,Draft

3,Closed

3,Open

];

flag:

left Join(input)

load * inline [

Status,value

Open,1

Accepted,2

Draft,3

Closed,4

Blank,5

];

minvalue:

Inner Join

load ID, min(value) as value

Resident input group by ID;

And you take the input table in table box. And you will get the desired result.

Thanks,
Sreeman

View solution in original post

Anonymous · ‎2016-02-15

you may try like this as well?

=if(Count({<Status={'Open'}>} ID)>0, Count({<Status={'Open'}>} ID),

if(Count({<Status={'Accepted'}>} ID)>0, Count({<Status={'Accepted'}>} ID),

if(Count({<Status={'Draft'}>} ID)>0, Count({<Status={'Draft'}>} ID),

if(Count({<Status={'Closed'}>} ID)>0, Count({<Status={'Closed'}>} ID),

if(Count({<Status={'Blank'}>} ID)>0, Count({<Status={'Blank'}>} ID))))))

sunny_talwar · ‎2016-02-15

Would you be able to share a sample with your desired output?

tinkerz1 · ‎2016-02-15

If my data is as follows:

ID Status

1 Open

1 Draft

2 Accepted

2 Draft

3 Closed

3 Open

My output is

ID Status

1 Open

2 Accepted

3 Open

Thats all

Thanks.

Chanty4u · ‎2016-02-15

hi try this,

use distinct function

=if(Distinct Count({<Status={'Open'}>} ID)>0, Count({<Status={'Open'}>} ID),

tinkerz1 · ‎2016-02-15

As an expression this is not working as its counting every status,

ID Status Count

3 Open 4

3 Draft 2

I would expect it to be

ID Status Count

3 Open 4

3 Draft -

Then I could filter out blanks

Chanty4u · ‎2016-02-15

chk supress null values

Anonymous · ‎2016-02-15

Did you try what I suggested you?

tinkerz1 · ‎2016-02-15

I did yes.

sasikanth · ‎2016-02-15

HI,

Need little more clarity on this ,

why did you suppress DRAFT related ID though it had count(ID)>0?

Are your following any order of the status ?

Can u pls explain a but more

Use AGGR in an dimension to return one result from an group