Datamodel problem loop

[Anonymous]

I have 3 tables which in a 'normal' datamodel are connected like this following:

ServiceOrders
Serviceordernr
CustomerID
..

Calls
Callnr
Serviceordernr
CustomerID
..
Customer
CustomerID
..

In QlikView this results in a loop ...

I want to show some things from the Customer view ... but also from Call and ServiceOrder view.

Problem I have is that a Call not always has a Serviceordernr ... and not all Serviceorders belong to a Call.

So when connecting the table Customer to Call I miss the information for customers on Serviceorder view.
When connecting the table Serviceorder to the information I miss the information for customers on Call view.

Don't know a practical way of solving this... anyone has suggestions??

I don't want to have the same Customer table twice (ofcourse with different naming) ... for selections in the same report this gives confusing situations ... and that is exactly what we don't want. I just want to choose once a CustomerID.

It is not possible to connect the tables ServiceOrders and Calls otherwise, since there are a lot of other tables involved too.

Anita

[#2]

JOIN the ServiceOrders and Calls tables.

[Anonymous]

This is exactly what we don't want, since these are two seperate objects we seperately use ...

If we join the two tables this means we need a lot of set analysis on the front-end to be sure to see the correct data ...

[johnw · Accepted solution]

This would actually change your data, so may not be what you want. But how about using the customer AS the order item when there's no order item in the call table. Add the customers to the order item table with a different order item type like 'customer call without order item'. You could then remove customer from the call table.

[Anonymous]

Did try to put the tables in one table ... but as I suspected (and you mentioned it also) this changes our data ... Something we want to avoid.

Since the report we where building is used for 2 different purposes which could be splitted up we decided to create 2 seperate reports.
This also takes away the problem which occurred ...

The first report now zooms in onto Calls - ServiceOrders and the second report on ServiceOrders (no Calls here). This way we don't have the problems we had ... and for our customers the reports are more clear since the goals of each report is now crystal clear ...

For future problems (did walk into this problems several times) I'll keep this in mind ...

Thx for the effort.