Data model problem

[#1]

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.

[lironbaram]

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

[#3]

Thanks for noticing!

Do you know how I can fix this?

[marcus_sommer]

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

[#5]

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?

[marcus_sommer]

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

[#7]

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?

[#8]

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

[marcus_sommer]

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

[marcus_sommer]

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