![]() ![]() ![]() When multiple tables are treated as one table, after the tables are joined, all tables are queried. In some cases, Tableau treats the multiple tables as one table. When you connect to and join multiple tables together, you set up a denormalized version of the data. For information about how Tableau handles combining data from different levels of detail (such as many-to-many relationships), see Cardinality and Referential Integrity. For more information about relationships, see Relate Your Data. When participating in #PreppinData challenges in 2019, it has struck me that there rarely (ever?) is one solution to a problem.Note: If your join is mis-counting data, it could be a sign you should be using a relationships instead. Looking at data for a sample of customers shows that the right shaped data has been acquired to support our subsequent analysis needs:Īs with so many things in Tableau, it’s interesting to compare my workflow to Andy’s to see where they deviate. The usual duplicate field removals precede the addition of an Output step, and we’re good to go. The final step prior to the Output stage is Joining the two separate Order Detail tables – a simple Inner Join on Customer ID: Next, identify the Second Order Date and Join that to the All Customer Orders aggregation to pull data for the Second Order. Start by establishing the First Order Date and Join that to the All Customer Orders aggregation to pull data for the First Order. ![]() Just a couple of duplicate removals and field name changes complete this step: ![]() Joining on Customer ID and explicitly stating that the Order Date in the All Customer Order aggregation equals the Second Order Date means we’re pulling the data we require. The workflow is getting a bit complex now, but I’m incredibly emboldened by my development in the past week as this is all making sense. We have now established our second Order Date per customer – now it’s time to Join this to the original All Customer Orders Aggregation: You can probably imagine what the next step entails. That’s nigh-on a replica of the First Order Date aggregation. Time to aggregate again, and use the MIN Order Date again: The second order that these customers ever made must therefore be the first in this new temporary view of the data. So now I have a temporary table showing me all of the second, third, fourth…. There are a couple of changes made too at this juncture: In my Not First Order Join, the Join Clause for Order Dates explicitly states “the Order Date is NOT the First Order Date” – it’s going to consequently show me ALL of the orders made by customers after their first order. The next step is the slightly more fiddly one: how to define the second Order Date for those customers who’ve made at least two separate purchases? That’s just a case of removing the fields which are duplicated in the Join process, plus making sure I make my Order Date and Sales fields unique to the “First” order, or when I join things further down the line it’ll get messy. I also carried out a few field renames and deletions: So an inner Join where the Customer ID from both aggregations tie up, and where the Order Date in the All Customer Orders aggregation is the same as the First Order Date in the First Order Date Aggregation. With these two steps in place, it’s now possible to ascertain the sales value for the first order date for each customer: Now that we know the first order date for everyone, we need a separate aggregation step which groups up all of the orders by customer and date:Īgain, simple enough. Instead of manually dragging your aggregated field over to the relevant pane, Prep Builder is smart enough that you can just select an aggregation method for your Measures over in the Additional Fields pane, and it’ll whack them straight into Aggregated Fields for you. earliest) Order Date (note below how I’ve renamed Order Date to First Order Date? That helps avoid getting confused with different Order Date instances later on):Ī minor quality-of-life tip picked up from Andy’s video was this. That’s a simple piece of aggregation where we aggregate and pull the MIN (i.e. This evening I’ve taken it on “blind”, doing things as logically as I can.įirst of all, it’s clear that a key of the challenge will be establishing what each customers first order was. I watched Andy’s video a couple of times and tried to recreate it in real-time alongside the video, but failed to nail it. Do customers spend more on their first or second order date? Using the stock Sample – Superstore dataset, we can interrogate the Orders table to find the answer. ![]()
0 Comments
Leave a Reply. |