Data Scaffolding using Alteryx

Question

Dear All

I am new to Alteryx and it's my first project which is on priority even though I didn't get time to get some training. My project needs to be delivered in a day or two. That's why I am posting my query over here and it's a request that please step by step guide me how to solve my puzzle.

In real life, I have 2.5 years historical data where we monitor performance basis Status of the job. I have attached an Excel sheet to understand it better. In Sheet 1, there's a one month data for one particular category. Over here, I need to check data basis different Status of the job. I am going to consider 3 types of jobs. When Status = 3 and PET = Null then it's called 'S', when Status = 2 and PET = 0 then it's called 'AE' and when Status = 2 and PET = 1 then it's called 'BE'. My data source is from SQL server and I need to build a Tableau dashboard out of this. One example of the issue is on 28th May 2022, we have data for AE but there's no other entry for 'S' and 'BE'. Due to this, Tableau is not considering that day's data and when I am calculating average of S, AE and BE for 28th May, it's skipping their data for 28th May. It's like the case of non existent data and it needs scaffolding. I need it to have a placeholder for those days so then I can convert it to 0 after that it will start counting that date for S and BE and my average would turn out accurate. It looks something like below snapshot:

Please guide me how should I proceed and thank you in advance.

Data.xlsx

mceleavey · Accepted Answer

Hi @jerry239 ,

I think I understand. You are missing a couple of lines where the data doesn't exist, and so there is no zero row in Tableau.

I've built a workflow that first applies the logic you describe to assign a job type per row. Then, I group the records by date and append a grouping off unique job types. This effectively creates a Cartesian join between job types and dates, and so creates every possible combination that SHOULD exist. I then join this back to the main stream of data. Anything on the right output is the combination of date and job type that does not exist. These records are then unioned back creating the null rows with only the date and the job type populated. This then shows you the ones for which you need the data.

I've attached the workflow. I hope this helps.

M.