Blending Data with Joins

[davechndr]

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:

1. Use the Input tool to load each data set.
2. Use the Join tool to combine data sets.
3. Use the formula tool to create a new field with the Product Group and Region.
4. 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

[BenMoss · Accepted solution]

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

[davechndr]

Hi @BenMoss,

I believe your way will work! When I looked at the solution to this problem, it is very similar to what I did before looking at the solution - I was so close!!

Though, I am not able to make the two fields combine in the Formula tool - namely the [Product Group] and [Region] fields, in this case. When using the syntax for concatenate, which is what's in both your reply and on the solution to this problem, I am getting two errors in red.

Specifically, one error message I am getting on the Formula tool is 'The field "" is not contained in the record. (Expression #1).' I do not know why this keeps recurring and I am not understanding why there even is an error, as I checked very carefully to make sure that the syntax I typed is correct to combine/concatenate the fields.

Could you please assist me, when you get a chance?

Thanks, again, for your guidance!

Best,

@davechndr

[BenMoss · Accepted solution]

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

[davechndr]

Hi @BenMoss,

Here is what I tried to do on this last week; no luck still.

Thanks, again, and talk later!

Best,

@davechndr

[BenMoss · Accepted solution]

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

[davechndr]

Hi @BenMoss,

I think I got it! Thanks for working with me on this - I really appreciate your help!

The only thing now is on my Output Data icon, it is giving me a file in the directory where I saved the database file (which is expected), but what format is the output file in? (It does not specify the file type for the output file, but I open the file in Notepad, or Wordpad, and strange characters appear. [Shouldn't I get 15 files, which would be Excel files?])

Thanks, again.

My best,

@dchandra