topic Re: One to many relationship in QlikView

One to many relationship

Tue, 01 Mar 2016 15:20:46 GMT

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

Re: One to many relationship

Anonymous — Tue, 01 Mar 2016 18:39:13 GMT

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.

Re: One to many relationship

Tue, 01 Mar 2016 18:47:12 GMT

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

Re: One to many relationship

maxgro — Tue, 01 Mar 2016 19:25:32 GMT

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.

Re: One to many relationship

Tue, 01 Mar 2016 21:53:06 GMT

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 !