topic datamodel loop ... i'm stuck [^o)] in QlikView

datamodel loop ... i'm stuck [^o)]

Anonymous — Tue, 09 Feb 2010 12:06:11 GMT

Hi all,

I'm working on a datamodel ... but I'm really stuck at this moment. Maybe because my database logic and knowledge differs from the way QlikView datamodel works.

See code below of the data as is ... problem with this model is that it turns into a loop ...


AVM_ErrorCode:
LOAD Department,
 Date,
 ErrorCode,
 ErrorCodeId,
 Description;
SQL SELECT Department,
 Date,
 ErrorCode,
 ErrorCodeId,
 Description
FROM Errorcodes;


ErrorCode_SO:
LOAD
 ErrorCodeId,
 Serviceordernr;
SQL SELECT
 ErrorCodeId,
 Serviceordernr
FROM `AVM Site History`;

ErrorCode_Call:
LOAD
 Callnr,
 Serviceordernr as ErrorCode_Call.Serviceordernr,
 FoutCodeId;
SQL SELECT
 Serviceordernr ,
 Callnr,
 FoutCodeId
FROM `AVM Call History`;
ServiceOrders:
LOAD
 Serviceordernr,
 .... (lots of extra data)
SQL SELECT
 Serviceordernr,
 ....
FROM SerficeOrders;
Calls:
LOAD
 Callnr,
 Serviceordernr,
 .... (lots of extra data)
SQL SELECT
 Callnr,
 Serviceordernr,
 ....
FROM Calls;

Did try some different things ... like adding errorcode to the ServiceOrder and Calls table ... but this still gives the problem the ErrorCode table is somewhere in between.

The ServiceOrders and Calls can be seen as two seperate items which are seperately used within our reports ...

The ErrorCodes are added to a ServiceOrder and to a Call ... these can be different since it are different objects.

e.g.

Call SO Call_errorcode SO_errorcode

CC01 SO01 123 620

Anyone can help me finding the light??

many thx,

Anita

datamodel loop ... i'm stuck [^o)]

Anonymous — Tue, 09 Feb 2010 13:15:47 GMT

To be more specific of the problem:

I'm a little stuck on how to find a good solution for the following "loop" between the following tables:

ServiceOrders

Serviceordernr

ErrorCode

...

Calls

Callnr

Serviceordernr

ErrorCode

...

ErrorCodes

ErrorCode

...

The ErrorCode mentioned in Calls and in ServiceOrders are different from each other.

Did think of combining the Calls and ServiceOrders but this is not the solution since Calls and ServiceOrders are different objects ... also the ErrorCode is for the ServiceOrder specific and for the Call (with Serviceordernr) specific.

When adding the ErrorCode information to the ServiceOrders and Calls table also the problem is there of how to make a selection on only the ErrorCode over both tables ...

Also did think of making a seperate table which contains the combination of the ErrorCodes but still there is the problem of 2 different ErrorCodes one for Call and one for ServiceOrder.

many thx

Anita

datamodel loop ... i'm stuck [^o)]

Tue, 09 Feb 2010 14:13:04 GMT

Hi Anita ,

If you dont want to link Errorcodes between two tables , then you can use "Qualify / Unqulaify " functions.

You can examples in HELP to demonstarte the same .

Mentioned below is just brief for you :-

The automatic join between fields with the same name in different tables, can be suspended by qualifying the field name with its table name. After qualification of a field name its name will be given in the form of tablename.fieldname.Qualification is switched off by default. It is switched on by means of the qualify statement. It can also be switched off again by means of the unqualify statement.

Regards,

Bhushan N

datamodel loop ... i'm stuck [^o)]

Anonymous — Tue, 09 Feb 2010 14:17:16 GMT

did already think of the qualify / unqualify ...

but I need data from the ErrorCode table ...

e.g. there is a department which i can select, in this case I need all ServiceOrders and all Calls with an ErrorCode for that department.

Only thing I came up with now is temporary create some sort of key and check fields for null values or not 😞

Anita