Filter by measure *or* Create field using measure

[CreepyCatLady]

Hello.

I have a measure (two, actually, but I think if I can get an answer on one it will work for both) that I need to be able to filter on in a table and I have not been able to get anything to work.

The measure is:

Count(total <USER> distinct MACH_NAME)

I need to be able to filter on whether MACH_NAME is >1, but since it is a measure, I can't filter on it. I tried aggr(Count(total <[PRIMARY USER]> distinct HOST_NAME)) but it gives me the error message "Aggr takes 2 parameters" and I don't know what I'm missing.

Ideally, I would like to create a new field in my data load called MACH_COUNT, but when I tried that, the field gives me incorrect counts. The code I used for that was:

MACH_COUNT:
Load USER,
count(DISTINCT (MACH_NAME)) as MACH_COUNT
resident [TABLE NAME]
Group by USER;

This gives me a successful load but the counts are off. 

I would be happy with a solution to either properly create a table dimension from the measure, or to correct the load of the new field.

Thanks in advance for any help. 

[stevedark]

Hi @CreepyCatLady 

The aggr function requires a parameter to say which dimensions you want to aggregate over.

So if you wanted to count how many hosts have more than one machine you would have:

count(aggr(if(count(DISTINCT MACH_NAME) > 1, HOST_NAME, null())), HOST_NAME))

The way to think of an aggr is that it will create a table in memory with the comma separated field names as dimensions and the first parameter as the measure. The code above will create a two column table with all host names in one column and either the same host name or null in the second column. The count then counts the second column (it's always the measure that is referenced by the expression outside).

The GROUP BY code looks correct, provided what you want is a table with each user listed and a count of machine names next to each user.

In what way do you think the numbers are incorrect? Adding a STORE statement to write the grouped table to CSV may make it easier to debug what is going on in there.

Hope that helps.

Steve

[CreepyCatLady]

Thank you so much for this explanation! I'm still having trouble but the fault is mine, I wasn't consistent in what I changed my example field names to. 

In the aggr function my example should have been aggr(Count(total <USER> distinct MACH_NAME)). So based on your answer, I tried 

count(aggr(if(count(DISTINCT MACH_NAME) > 1, USER, null())), USER))

and it is still telling me I need at least 2 parameters. So I clearly didn't translate what you said correctly. 😕 

Regarding the GROUP BY code, I can determine that it is giving incorrect numbers based on a spot-check with the raw data. Users with 3 machines are showing up with 5, single-machine users are showing up with multiple machines, etc. Every errant example I saw in the spot-check was showing more machines from the loaded field than exist in the data. So I have no idea what is going wrong with that.