Stitching dimension when there is no facts available

This might be a classic requirement where users want to see dimensions all the time regardless if there is a fact reported. I am trying to get my Accounts listed for each week regardless of Sales reported against them.

As you could see in the 'Fact' for 2021 Week - 2 I don't have Sales reported for C & D. I am referring to my entire list of Accounts in another table (Master Account List) . I am looking to do this sort of scaffolding only for my latest year.

For this example Latest Year is 2021 and I wanted to see all Accounts (A,B,C and D) against all the weeks if Sales is not reported in at least one of the segments. e.g. For Week 1 (2021), Accounts A,B and C has at least one segment reporting Sales, similarly for Week - 2 A & B is reporting Sales for at least one. Scaffolded dataset may not need to have Segment and Sales information filled since it may not be appropriate. However it should have Year & Week filled so that any Account wont be missing for any week.

I have attached spreadsheet with sample data (Fact & Master dimension) also Current vs Expected screenshot how the data layout will be for clarification. Thank you for the help with this.

Alteryx_Scaffold.xlsx

Current vs Expected.png

Datasets

Common Use Cases

Transformation

Data Investigation

Accepted answers

Christina_H

Is this what you're after? Making sure that every account name is included in every year/week in the data.

Stitching.yxmd

All comments

pdave87

@AlterIT hi, great question. I believe below will address your use case. We need one text input to create all dimension and once that is ready we can union master dimension with input sales data and create a summarized group for all dimension that will provide below result. #2 is master dimension with exhaustive list of Account, Segment, Year and Week (That can be achieved in similar method if the sources are different)

Happy solving 🙂

AlterIT

@pdave87 Thanks for helping on this. One challenge I see with this one is I need to add Week as part of master dimension which is not really I am looking for. I need to allow weeks to reflect based on data availability. e.g. Lets say I have fact available only until week 2, I should only be stitching the data until Week 2 and not beyond that.

pdave87

@AlterIT I am happy to be part of this community & learning everyday 🙂

Back to your solution: we can use filter condition on week based on requirement <= 2 before the step in creating output. Will this address your final output? We can also address this via standard macro and pass it on to users requiring this output within the organization 🙂 But that's more advanced step we can look into if required.

Regards,

Pratik

AlterIT

What I really meant to say is , that week is dynamic based on fact availability. Let’s say we have fact until week 10 , this data filling process need to happen only until week 10 and so on

pdave87

That can be considered as iteration limited to x number, we can address that through macro as an exit condition?

Christina_H

Is this what you're after? Making sure that every account name is included in every year/week in the data.

Stitching.yxmd

AlterIT

@Christina_H That's what exactly I was looking for.

One addition to the solution I am looking for is, how can I do this stitching/scaffolding only for the latest year available in the Fact. In this example this Account stitching should only work for 2021. So finally historical data would be "as is" and latest year's data will be scaffolded with missing accounts.

Thank you for the help !!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703