One to many relationship

[#1]

Hi all,

I am currently stuck on a logic for which I need help from you guys.

I have two tables and there is one to many relationship between them. Please see below the table structure.

One asset can have multiple contract.

I am trying to show everything in a straight table. In straight table I want to see 1 record for each asset. Please see below for the logic I am trying to implement.

An asset could have many different type of contracts associated with it. Please see below for the different scenario.

• An asset can have only one 'Active' contract -> in that case I want to show it.
• An asset can have one 'Active', one 'Past', one 'future' contract -> in that case I only want to show active.
• An asset can have more than one 'Past' contract -> in that case I only want to show the latest past contract.
• An asset can have 2 'Active' contracts, 2 'Past' contract and 2 'Future' contract associated with it. -> in that case I only want to show 2 'Active' contracts.

In summary, if an asset has 'Active' contract I only want to see all Active contracts for that asset but if an asset has no 'Active' contract and more than one 'past' contract than I only want to see latest past contract.

Please let me know how to achieve this. I greatly appreciate your help.

Please let me know if you need any more information.

Thank you,

Parth

[Anonymous]

In the load script for your Table2 - Contract I would create a Contract Status Dimension for each for row with possible values of :

• Active
• Past
• Future

Then you should be able to visualize as per your wonts with Set Analysis.

Better though would be in your load script to add a flag for Display Asset using Group By and the Peek() function.

[#3]

Hi Bill,

Thanks for your reply. Sorry as I didnt make it clear, Type field in Table 2 - Contract has these values -> Active, Past, Future.

I already created a flag called numberOfContracts which is the count(distinct contract_id) group by asset_id and left join with the contract table

So, my Contract table now has one more field called numberOfContracts.

What should I do next ? how do I implement such logic which answers all the possible scenario I described?

Thank you once again,

Parth

[maxgro]

I think you have to flag all the active contract by asset i f there is >= 1 active contract (by asset)

or, if you don't have active, the last past

If you post some data, someone can try in the script.

[#5]

Hi Maxgro,

Please find the attached spreadsheet containing dummy data and the expected result. Please let me know if you have any questions.

Thank you so much for your help,

Parth !