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

Question

Basket IDStore IDItem CodeItem AmtPrice1234598712345678924.81234598712345679013.51234598712345679112.4236789872546511.22367898712345679310.8236789879564353194567865745676878110

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?

Chris_Kirchberg · Answer

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/30755

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

icecold546 · Answer

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.....

Chris_Kirchberg · Answer

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

Basket.jmp