Hello, I've ported in a fairly simple spreadsheet which contains such values as: "Incident ID", "Site", and "Reported By". I'd like to create a flag that would be used to tabulate when the "Reported By" value has 4 or more Incident IDs associated with it. With someone here's help, I have this in the data load editor:
AGGR:
Load "Reported by", if(Count("Incident ID") >= 4, 1, 0) as Flag Resident [Record Extract] group by "Reported by";
Inner Join([Record Extract]) Load "Reported by", Flag resident AGGR;
drop table AGGR;
This code runs fine but it does not tabulate the values I would expect.
For instance, this chart function yields me a result of 230:
Count({<Site={'Dallas', 'Houston'}, Year={'2021'}, Department={'Human Resources'}>} [Reported by])
This means there are 230 instances of a reporting instance for this year, department and location. This is consistent with the data.
This chart function ALSO yields me a result of 230:
Count({<Site={'Dallas', 'Houston'}, Year={'2021'}, Department={'Human Resources'}>} [Flag])
This doesn't make much sense to me, as the data load editor script should be making a count of '1' for when a "Reported By" value satisfies the condition. This should divide the data roughly by 4 (but not completely, since not everybody has 4 or more instances associated with them). The result should be somewhere around 50. The 'Flag' is clearly not functioning as intended, but I'm not sure why. Does anyone see anything obviously wrong about the code? Thank you in advance!!
