Forum Discussion

Arcturus's avatar
Arcturus
Helper I
8 years ago
Solved

Understanding Relationships

Hello everyone,   I am having a hard time understanding how relationship work, what they do, etc     My situation is as follows: Say I have a query containing sales data at the geographic level,...
Need Help
  • Chris99's avatar
    Chris99
    8 years ago

    Hi Arcturus

     

    My first thought would be to go for

    • Sales table that contains a unique id, a sales date, an amount, a product id, and a location id,
    • Product table that contains a product id and a description
    • Location table that contains a location id and a description

    Then create the following relations

    • Sales product id to Product product id (Many to one)
    • Sales location id to Location location id (Many to one)

    That should get you going, and then later you can flesh it out, maybe by adding a region to your Location table, or adding a new Salesperson table and a salesperson id to the Sales table.

     

    Hope this helps,

     

    Chris

  • Chris99's avatar
    Chris99
    8 years ago

    If you want a date table then you should have a single row per date. The error suggests that the date table has duplicate dates.

Arcturus's avatar
Arcturus
Helper I
8 years ago

Thanks for offering help Chris99, much appreciated!

 

Currently there is none (I am training on fictious data sets that I have created).

 

Could it be done w/o one? Maybe by connecting the dates, which are 100% identical in each query and occur in both.

 

If not, could you please confirm my understanding of the way it would work:

- I would need to create an extra identical column in each query table, containing Sales ID.

- I would need to create a relationship linking the Sales ID from the geographical query, to the Sales ID of the sales query (Not sure this is right, as I recall not being able to create relationships between identical fields)

- I would need to set the relationship to be a one to one, as each Sale ID will be unique

- I would need to set the relationship to be both cross-filtering as there is information from both table to be used in the visualisations.

 

Thanks again!

 

Chris99's avatar
Chris99
Advocate III
8 years ago

Hi Arcturus

 

My first thought would be to go for

  • Sales table that contains a unique id, a sales date, an amount, a product id, and a location id,
  • Product table that contains a product id and a description
  • Location table that contains a location id and a description

Then create the following relations

  • Sales product id to Product product id (Many to one)
  • Sales location id to Location location id (Many to one)

That should get you going, and then later you can flesh it out, maybe by adding a region to your Location table, or adding a new Salesperson table and a salesperson id to the Sales table.

 

Hope this helps,

 

Chris