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.

[annafuksa1]

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

[#3]

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

[annafuksa1]

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.

[#5]

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

[annafuksa1]

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.

[#7]

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