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

[stevelogue1980]

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 :

[grazitti_sapna · Accepted solution]

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.

[stevelogue1980]

Thanks for the Replay.

Perhaps,After loading these Tables in Target, If we want to Join we need  PK,FK relations between these 3 Tables?

EX: Needs a State ID & City ID in CIty DIM to join with Fact.Not sure how to generate these ID's

[ImadZidan · Accepted solution]

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.