Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

afuchten
Valued Contributor

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

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

Tags (2)
3 Replies
afuchten
Valued Contributor

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

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

Not applicable

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

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

afuchten
Valued Contributor

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

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