Summarize and Joining back

[smoskowitz]

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:

1. The total for all clients in a country is >=100000 and...
2. A client is in more than three countries
3. 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.

[LordNeilLord]

#BossMoss wins again!