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.

[BenMoss]

Given your sample input data, what does your desired output look like?

If you could provide us with that then I think we are more likely to be able to find a solution for you!

Ben

[smoskowitz]

Ben --

The ones marked global client would meet the criteria. 

This client is in 3 or more countries whose group total by country is >=1000. The label Global Client is appended to these three countries as they meet the criteria, while the other countries for this client do not and there are blank in column s 

Thanks,

Seth

[BenMoss]

Thanks Seth,

And what field represents the client identifier?

Ben

[smoskowitz]

If the client is in 3 or more countries total for a country and a client has  >= 100000 

So if you look at the data for this client, the total for 2016, and you summarize by year and country, the total for US, UK, and India is >=100000. It, therefore, gets the global client designation.

As you can see there are also other countries for the client, but there total in the value field is less than the 100000 minimum.

Seth

[BenMoss · Accepted solution]

Okay I think I have it.

In order to the client to get the 'Global' tag it must have AT LEAST three countries where the total value is greater than 100,000.

I think this workflow should provide the information, or at least get us part way to the solution!

[BenMoss]

I've also made the assumption that the 'name' field is our client ID, but if this is wrong then we should replace name with the true client ID across the workflow.

[smoskowitz]

That is an accurate statement.

thanks,

Seth

[smoskowitz]

I looked at the data and it's close, but not quite right. It is tagging clients with less the 100000 as global clients. Like Canada and Malaysia. US, UK, and India are the only ones that meet both criteria. Canada and Malaysia both have less than 100000 and should be marked as a No. We are almost there.

Thanks,

Seth

[BenMoss]

Okay, so I beleive the 'country tag' field that already exists in the version I sent previously should answer that question. It is given as 'Yes' and 'No' but could easily be changed to 'Global Client' and NULL() accordingly.