It’s World Statistics Day! To honor the theme of the day, the JMP User Community is having conversations about the importance of trust in statistics and data. And we want to hear from you! Tell us the steps you take to ensure that your data is trustworthy.
Choose Language Hide Translation Bar
Highlighted
Level I

## How to join\Update Multiple data tables using same Match ID

 Basket ID Store ID Item Code Item Amt Price 12345 987 123456789 2 4.8 12345 987 123456790 1 3.5 12345 987 123456791 1 2.4 23678 987 25465 1 1.2 23678 987 123456793 1 0.8 23678 987 9564353 1 9 45678 657 45676878 1 10

I have for example the above data set of a supermarket chain, how do I work with JMP to be able to get a total price of a basket, or the total revenue of a store?

3 REPLIES 3
Highlighted
Staff

## Re: How to join\Update Multiple data tables using same Match ID

Hi,

I am going to assume here Item Amt is the number of that item in the basket and Price is the cost per item. Also, the data is in JMP, Item Amt is designated as Numeric for Data Type and Nominal for Modeling Type (Right Click and choose Column Info.), For Price, it is Numeric Continuous and I used Currency as Format.

With that said, first we will need to multply the Item Amt by Price. This can be done with a column formula. To make this simple, I select both Item Amt and Price columns. Then I Right Click on one of the columns and Choose New Formula Column>Combine>Product. This will make a new column that multiplys both columns together.

Now that we have a total amount for that item in the basket, I can use Tablulate (Analyze>Tabulate) to summarize by Basket ID or by Store ID.  It is a drag and drop summary table type tool. I drag the Basked ID column into the Drop Zones for Rows and then I drag and drop the new Column (Item Amt*Price) to the top (where the column header is called N).

I now get a summary table for total amount (sum) of each basket. I could do the same for Store ID.

In fact, I can nest Basket ID within Store ID, by draging the Store ID column to the left of Basket ID column header in the summary table (you will see a thin blue line appear letting you know its is ok to drop the column there).

I created a JMP Data Table in JMP 13 and attached it here. I also saved the tabulate report to the data table (look for the green triangle in the upper left next to the word tabulate and click on it to run it).

Result looks something like this:

Is this what you were looking for?

Chris

Chris Kirchberg
Principal Systems Engineer, Life Sciences - JMP Global Technical Enablement
SAS Institute, Inc. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
JMP – A Division of SAS Institute | www.jmp.com
Highlighted
Level I

## Re: How to join\Update Multiple data tables using same Match ID

Yes that part I got to. It gets more complex because there are "millions" of baskets, and so it becomes difficult to deal with them like in a small table. Each item ID has a "type" of item linked to it which I linked via virtual table. How would one analyse the composite of many baskets? Or run statistics on every basket (i.e. mean value, median, std dev, etc). Each store is located in different states, so I wanted the basket to see if there is variations in buying patterns between states.....

Highlighted
Staff

## Re: How to join\Update Multiple data tables using same Match ID

Ah, and the devil is in the details. First, glad you found the virtual join. Do tou have store informaton in a separate table too and linked to this main table by Stored ID as well?

Under Table menu, there is teh Summary option which is faster for generateing Mean, Median, Std. Dev., etc. Summary statistics. From the perspective of analyzing the data for patterns, when that is a different kind of analysis.

Are you asking how to do Market Basket Analysis in JMP?

A recent blog about this can be found here:

https://community.jmp.com/t5/JMP-Blog/JMP-13-Preview-Association-analysis-for-analyzing-sparse/ba-p/...

If you have the large Basket list of items and store information linked (virtual join) to the Item and Store details tables, they should appear as columns in you rmain table so you can perform these types of analyses.

Is this something more like what you are hoping to do?

Chris

Chris Kirchberg
Principal Systems Engineer, Life Sciences - JMP Global Technical Enablement
SAS Institute, Inc. - Denver, CO
Tel: +1-919-531-9927 ▪ Mobile: +1-303-378-7419 ▪ E-mail: chris.kirchberg@jmp.com
JMP – A Division of SAS Institute | www.jmp.com
Article Labels

There are no labels assigned to this post.