Gentle and wise Qlik experts,
Please will you help with the small conundrum that I have as it is not processing in my mind:
I have a schema that works for identifying which components of a system are related to the others... Hardware, Database, SW, to Business Application where:
Application has 1 or many SW
SW has 0, 1 or many HW (as HW1)
SW has 0, 1 or many DB,
SW has 0, 1 or many related SW (as SW2)
DB has 1 or many HW as well (identified a HW2 - this may also have been identified in as a HW1)
A simplified view of the relationships as a table (without having to draw a snowflake diagram)
| Application | SW | HW1 | DB | SW2 | HW2 |
|---|
| App1 | A1SW1 | STV11 | UKPRD1 | - | STV12 |
| App1 | A1SW2 | STV12 | - | - | - |
| App2 | A2SW1 | BRE21 | - | - | - |
| App3 | A3SW1 | STV12 | UKPRD2 | - | BRE22 |
| App3 | A3SW2 | STV11 | - | A3SW3 | BRE22 |
Each SW, HW type and DB type has a cost, which I join to or duplicate for the SW, SW2, HW1, HW2 and DB attributes tables
Just using the HW as an example:
The issue that I have is that in order to view/report the apportioning (% cost) HW cost to the Applications I have to find out which Application a HW1 or HW2 is assigned to - easy so far, but I can not double count that HW where it appears as both HW1 and HW2 => case above where STV12 is both HW1 and HW2.
Any suggestions of how to have a table below? as HW column pulls from 2 branches of the snowflake :
| HW | Cost for HW Type | Amount to Apportion to each Application |
|---|
| from (HW1) or (HW2) or (HW1 and HW2) | Cost for the Type of HW (either HW1 or HW2) | HW Type Cost / count of related Applications |
| STV11 (in HW1) | £1000 | 1000/2 = 500 |
| STV12 (in HW1 and HW2) | £2000 | 2000/2 = 1000 (not 2000/2 for HW1 + 2000/1 for HW2) |
| BRE21 (in HW1) | £1000 | 1000/1 = 1000 |
| BRE22 (in HW2) | £2000 | 2000/1= 2000 |
Any hints discussion, pointers please respond.
All the best
Derek
