topic Datamodel problem loop in QlikView

Datamodel problem loop

Anonymous — Tue, 16 Feb 2010 11:14:25 GMT

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

Datamodel problem loop

Tue, 16 Feb 2010 12:20:54 GMT

JOIN the ServiceOrders and Calls tables.

Datamodel problem loop

Anonymous — Tue, 16 Feb 2010 13:03:30 GMT

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 ...

Datamodel problem loop

johnw — Wed, 17 Feb 2010 01:18:30 GMT

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.

Datamodel problem loop

Anonymous — Wed, 17 Feb 2010 08:36:10 GMT

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.