Hi All --
I have been working on a workflow and have run into a bit of an issue. Here is what I am trying to do. I have a data source where I have:
Year
Client
Country
Amount
The analysis I am trying to do is:
For each year and client get the total by country if:
- The total for all clients in a country is >=100000 and...
- A client is in more than three countries
- Ideally, I would need columns C-P also added in and an identifier in column R to identify rows that the rules are met.
The issue I am having is that figured I could add a RecordID and split my streams into tow summarize tools and then join back, but you really can's summarize by record Id. What I mean is - in one of my streams, I do a count distinct to get a number of countries for a client by year. If I add in a record ID then the count distinct is no longer accurate.
Also, at this point, the summarize has nothing unique (or even several columns that are unique where I could do a join. So I would have in one summarize year, client, and country and in the other stream year, client and distinct_country.
Any help you can provide would be appreciated. Sample data is attached.
