Alteryx Designer - How to Create Column ID's (PK/FK) to Join Tables

Question

Hello,

I am a new bee trying to load normalized tables from a file (attached) as per  new the below Star Schema to Populate Multiple small Tables like Facts & Dimensions using Alteryx Designer.

However, Cant load data in Target: FACT Can’t normalize date in Target :Date, and more date fields like Date DIM.

Please find the Source Files, and Alteryx file and appreciated if anybody help me with Alteryx workflow

Need this :

DesignTables.yxmd

TargetTest.xlsx

SourceData.xlsx

grazitti_sapna · Accepted Answer

Hi @stevelogue1980 , I have looked into your workflow and fixed things please check and let me know if this is what you were looking for?

Changes made- To standarize the date I formated the date column in the excel itself.

For Target fact table you were using ROWID to generate row which was not satisfying your  Condition expression. Therefore, I created a New Field  NewID in order to make it work.

Thanks.

ImadZidan · Accepted Answer

Hello,

See attached. it is based on the sourceData file you provided. The workflow will generate 3 tables or files. DimDate with id that you can use to join to the main table. Also Dim city which include city name and state id. I would imagine you need another lookup table for the state name. You can derive the state table from the workflow if you need. Meanwhile, have a look.

I hope it helps.