Blending Data with Joins

Question

Hi everyone,

I was trying an exercise involving data blending, where joins are involved and I am stuck. In quotes below, is the exercise description with hints (typed in bold, orange text, underneath). I have attached whatever I have constructed to try it out! :+)

"A company has 3 disparate sources of data that have common identifiers between them.

1) \clientpurchasedata.sas7bat: with customer purchase data;

2) \ProductGroupings.yxdb: containing a product group look up table;

3) \Regions.csv: with dollar amounts by region.

Your goal is to merge ALL 3 sources to generate 15 unique outputs for each Product Group by Region.

The Product Grouping Table and Customer Purchase Table can be merged on a common field containing the Product Group Lookup ID.

The Regions Table can be merged with the Client Purchase Table based on a Region Field.

The final outputs should contain the following fields:

Product Group

Client

Region

Purchase

Total Market

The File Names should bollow the following format:

Product Group_Region_Out.csv...                                                              "

Hints:

* Use the Input tool to load each data set.
* Use the Join tool to combine data sets.
* Use the formula tool to create a new field with the Product Group and Region.
* Use ONE output tool to generate all output files.

I do not understand why or how a formula tool to do what Hint #3 says would help produce the desired output; also, can anyone explain how 15 unique outputs with the Product Lookup ID can be generated by the three inputs? (I am not seeing that.)

Any ideas would be greatly appreciated!!

Thank you so much!

Best,

@davechndr

Data_Blending_Exercise(Home_Improvement_Store).yxmd

BenMoss · Accepted Answer

So deciphering the last part for you.

'use the formula tool to combine the two fields together'

I believe what they want you to do is create a new field which concatenates two strings together; this can be achieved by using a simple statement like...

[Field1]+'_'+[Field2]

In terms of outputting 15 files from one output my guess is that the field generated above creates 15 unique combinations over the entire dataset.

Using one output tool if you look at the bottom there is the option to 'append field value to table name' or something along those lines. Chech this option and choose your newly generated field, you will then get n number of files from your single output tool based on the number of unique values in that field!

Have a go

Ben

BenMoss · Accepted Answer

Hi @davechndr

I'm sure you understand but I will need to see all of the syntax used to highlight where the problem is!

Ben

BenMoss · Accepted Answer

In the formula tool you have not declare a field to update/create, you see it says 'select column'. Click there and then select 'Add Column', then name the column and everything should work!

Ben