topic Data model problem in QlikView

Data model problem

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

Im having a small problem with my data model.

I created a field called 'tooltip' by subtracting some characters from another field called 'message'.

Now when I load the data and select the tooltips that are logged per year/month, it shows the wrong data.

I think it has something to do with the associations used in my data model between the audit_NL and Session_NL tables.

The script is as followed.

LOAD
If(SubStringCount(Document, '_') = 5, Capitalize(Textbetween(Document, '_', '.', 5)), Capitalize(Textbetween(Document, '_', '.', 4))) as Module,
If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 1, 4), mid(Timestamp, 1, 4)) as Year,
If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)) as Month_nr,
Month(Date#(If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 5, 2), mid(Timestamp, 6, 2)), 'MM')) as MonthName,
If(WildMatch(Timestamp, '*T*'), mid(Timestamp, 7, 2), mid(Timestamp, 9, 2)) as Day,
If(SubStringCount(Document, '.')= 4, Textbetween(Document, '_', '_', 3), Textbetween(Document, '_', '.', 3)) as Debtor,
[Identifying user] as User,
[Authenticated user] as Sub_User,
AutoNumberHash128(Timestamp, [Identifying user]) as Timestamp.User,
Timestamp

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq)

WHERE(WildMatch(Document, '*rna*')) //or Wildmatch(Document, '*workforce*'))
;
Session_NL:

LOAD
Date(Monthstart(Timestamp), 'MMM/YYYY') as YearMonth,
* Resident Session_NL_Temp

;
DROP Tables Session_NL_Temp
;

Audit_NL:
LOAD
purgechar(If(Wildmatch(Message, '*TooltipId*'), mid(Message, 28, 2)), ',') as Tooltip,
User,
Message,
'Yes' as Used
FROM

(txt, utf8, embedded labels, delimiter is '\t', msq)

WHERE(WildMatch(Document, '*rna*'))
;

Chart:
LOAD
Object as Item,
Name,
Favorite
FROM

(ooxml, embedded labels, table is Charts);

Chart_file:
LOAD
Dutch as Standard_Sheet_Object,
Item,
Notes
FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate (Chart_file)
LOAD
English as Standard_Sheet_Object,
Item,
Notes
FROM

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN (Chart_file)
LOAD
Replace(Replace(Replace(SubField(If(WildMatch(Message, '*_Chart:*'), Message,
if(Wildmatch(Message, '_Dashboard: *'), Message,
if(WildMatch(Message, '*_GUI*'), Message))), ','),'_Chart: ☆ ',''), '_Chart: ', ''), '☆ ', '') as Standard_Sheet_Object,
Used
Resident Audit_NL
;

I'll add the qlikview script and an image of the datamodel as an attachment.

Re: Data model problem

lironbaram — Thu, 05 Jan 2017 08:21:38 GMT

according to your model

selecting a tooltip will filter the fields users,used

there seems to be no connection between this field and your dates field

Re: Data model problem

Thu, 05 Jan 2017 08:26:33 GMT

Thanks for noticing!

Do you know how I can fix this?

Re: Data model problem

marcus_sommer — Thu, 05 Jan 2017 08:32:49 GMT

I think you need to create also an appropriate date/time-field within the audit-load and combine this field with a User as Key and creating the same Key within your session-load.

- Marcus

Re: Data model problem

Thu, 05 Jan 2017 08:37:19 GMT

So that would be something like this?

Audit_NL:

Autonumberhash128(Timestamp, User) as Timestamp.User

Session_NL:
Autonumberhash128(Timestamp, User) as Timestamp.User

and then make sure that both of the original field names from user and timestamp have

a different name to prevent circular referencing?

Re: Data model problem

marcus_sommer — Thu, 05 Jan 2017 08:54:36 GMT

Quite similar but I would rather not load User and Timestamp within the audit-load else only the Key and would at first create the Key with Timestamp & '|' & User to if it worked like expected (autonumber-wrapping could be applied if it worked).

I'm not sure if the timestamps are really synchron and you might be more and different timestamps within the audit-data than in the session-data because their kind of logging is different. This meant you might need to modify one or both timestamps and/or match the audit-timestamp to their belonging session.

- Marcus

Re: Data model problem

Thu, 05 Jan 2017 09:03:47 GMT

Both Timestamp are the same in the Session and Audit data.

So you suggest to just only load the user and timestamp in the Session Data and then combine the

two in the audit data and make a association with a combination of timestamp and user in the session data?

Re: Data model problem

Thu, 05 Jan 2017 09:14:39 GMT

I tried this, but it doesn´t work. It gives me data, but not the right data.

Re: Data model problem

marcus_sommer — Thu, 05 Jan 2017 09:22:51 GMT

Put the Key + User, Timestamp and Tooltip into a tablebox and then you should see which data are matching and which not and from there hopefully to notice why not and with them you could correct it.

- Marcus

Re: Data model problem

marcus_sommer — Thu, 05 Jan 2017 10:03:21 GMT

One addition: I think the used fields for the key won't be sufficient - there must be included the application then an user could be open and interact with several applications at the same time.

- Marcus

Re: Data model problem

annafuksa1 — Thu, 05 Jan 2017 10:27:23 GMT

I most of a case using Timestamp its not a good idea and its not recommended. Could you post your app that we can see the data and help you find the right keys ?

Anna

Re: Data model problem

Thu, 05 Jan 2017 10:42:01 GMT

Unfortunately I can´t share the data document with you, due to secrecy of the company that I work for. I can post all the column names so you can see what options are possible.

Ivo

Re: Data model problem

annafuksa1 — Thu, 05 Jan 2017 11:26:54 GMT

Columns names are not always useful but you can send them. My idea was to look at the data to find more potential keys as I said Timestams are most of a time not a good key as many databases generate them automatically, its good to not to load them into model as can be confusing , generate synthetic keys and loops.

Re: Data model problem

Thu, 05 Jan 2017 11:47:39 GMT

Thanks for putting time into this!

These are the field names by table

Session_NL

[Exe Type],

[Exe Version],

[Server Started],

Timestamp,

Document,

[Document Timestamp],

[QlikView User],

[Exit Reason],

[Session Start],

[Session Duration],

[CPU spent (s)],

[Bytes Received],

[Bytes Sent],

[Calls],

[Selections],

[Authenticated user],

[Identifying user],

[Client machine identification],

[Serial number],

[Client Type],

[Client Build Version],

[Secure Protocol],

[Tunnel Protocol],

[Server Port],

[Client Address],

[Client Port],

[Cal Type],

[Cal Usage Count]

Audit_NL

Server Started,

Timestamp,

Document,

Type,

User,

Message

Re: Data model problem

annafuksa1 — Thu, 05 Jan 2017 13:42:33 GMT

between this table you have fields that can be a keys Document ,

if you do not want to post data you can debug the application and load like 100 lines

or export to excel and change them a little (change a the text )

names of a fields for table Audit_NL looks a little different then on your print screens.

Re: Data model problem

Thu, 05 Jan 2017 14:10:05 GMT

Thanks. I will try to use Document as a Key.