topic Re: Left Join + IntervalMatch causing count() to be incorrect? in App Development

Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Wed, 26 Sep 2018 20:20:05 GMT

Hello all,

I'm attempting to associate a timestamp in one table with the shift date in another at the bottom of the code posted below.

If I remove the underlined portion of the script(the portion that associates the two tables), I will get the correct count() when setting up my dimension on a bar graph but of course then the shift is no longer connected to the timestamp. The numbers are not even close it goes from a total of 10k for one error message to 7.2 million.

Any insight into why this is happening, suggestions for alternative ways to do this, or critiques of other portions of the script would all be helpful.

[log]:
LOAD
    SubField(Object,',',1) as Machine,
    SubField(Object,',',2) as Order,
    SubField(Key,',',1) as Product,
    SubField(Key,' ',2) as Code,
    SubField(Key,'-',2) as [Error Message],
    Text,
    "Timestamp",
    "UserID",
    NewValue As Comments;
SQL SELECT
    Object,
    Key,
    Text,
    "Timestamp",
    "UserID",
    NewValue
FROM Plant.dbo.Log
WHERE Text LIKE 'O%';

[Shifts]:
Load
ShiftPlannedStart,
    ShiftPlannedEnd,
    Replace(Replace(Replace(Replace(Replace([ShiftName],'A','1'),'B','2'),'C','3'),'D','4'),'E','5') as [Shift];
SQL Select
ShiftPlannedStart,
    ShiftPlannedEnd,
    ShiftName
FROM Plant.dbo.Info;

Temp:
Left Join (log)
IntervalMatch("Timestamp")
Load ShiftPlannedStart, ShiftPlannedEnd
Resident Shifts;

Left Join (log)
Load *
Resident Shifts;

Drop Table Shifts;

Re: Left Join + IntervalMatch causing count() to be incorrect?

mikaelsc — Wed, 26 Sep 2018 21:09:08 GMT

why rush to the removal of the intervalmatch() resulting table?

i know it's a synth key... but start by assessing the matching worked.

just try

IntervalMatch("Timestamp")

Load ShiftPlannedStart, ShiftPlannedEnd

Resident Shifts;

without any additionnal join.

(and if the results are wrong, begin checking formats and interpretation of timestamps)

(also are your shifts mutually exclusive / no overlapping? )

Re: Left Join + IntervalMatch causing count() to be incorrect?

swuehl — Thu, 27 Sep 2018 07:36:54 GMT

In addition, what is the dimension and the field you are counting (with or without DISTINCT?)?

Have you checked that the Timestamp is actually linked to incorrect shift time spans?

If your shift table contains a lot of duplicates (for example, records per worker, but worker is not loaded), this also might explain an increased count().

Re: Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Thu, 27 Sep 2018 11:36:29 GMT

Thanks for the input Mikael,

I've modified the last portion of the script to be...

Temp:
IntervalMatch("Timestamp")
Load ShiftPlannedStart, ShiftPlannedEnd
Resident Shifts;

Hopefully this is what you where suggesting to try. As you mentioned it did create a synthetic key, which from what I understand people usually try to avoid because of performance/memory issues?

The data manager and "load" function both completed in a short period of time so I don't have any concerns there.

The modified script above accomplished exactly what I wanted, I have all the associations I need as well as the correct count. Thanks for the help.

Do you have any idea why the original script was causing an issue using the count() function?

Re: Left Join + IntervalMatch causing count() to be incorrect?

vvvvvvizard — Thu, 27 Sep 2018 11:38:12 GMT

Try

Temp:

inner Join (log)

IntervalMatch("Timestamp")

Load ShiftPlannedStart, ShiftPlannedEnd

Resident Shifts;

Re: Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Thu, 27 Sep 2018 11:54:44 GMT

In addition, what is the dimension and the field you are counting (with or without DISTINCT?)?

Originally I was counting "text" but have tried other dimensions such as "code", and "machine".

"Text" is a column filled with the same data in every row. My purpose of using Count() was to get the total of every occurrence but I did try Count(Distinct just to see the behavior and as expected it only count 1 instance per day.

Have you checked that the Timestamp is actually linked to incorrect shift time spans?

The timestamp is linked to the correct shift spans.

If your shift table contains a lot of duplicates (for example, records per worker, but worker is not loaded), this also might explain an increased count().

Currently I'm loading ShiftplannedEnd, ShiftPlannedStart, and Shift.

ShiftPlannedEnd or ShiftPlannedStart should not have any duplicates as it is the a combination of a date with a specific time

ShiftPlannedStart

1/1/2014 2:30PM

1/1/2014 10:30PM

1/2/2014 6:30AM

1/2/2014 2:30PM

ShiftPlannedEnd

1/1/2014 2:29:59PM

1/1/2014 10:29:59PM

1/2/2014 6:29:59AM

1/2/2014 2:29:59PM

Shift does contain a significant amount of duplicates as, depending on the ShiftPlannedStart it will be either a 1-5 for every entry.

Your saying this could be the reason for the count() increase?

Re: Left Join + IntervalMatch causing count() to be incorrect?

swuehl — Thu, 27 Sep 2018 12:01:01 GMT

If you select a specific shift (Shift start and end timestamp), what does a

Count(ShiftPlannedStart)

return?

Re: Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Thu, 27 Sep 2018 12:10:36 GMT

So it definitely seems there are duplicates but I'm still unclear how and how this creates duplicates of other dimensions such as "text".

Re: Left Join + IntervalMatch causing count() to be incorrect?

swuehl — Thu, 27 Sep 2018 12:13:34 GMT

Wouldn't we expect a unique record per shift?

Re: Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Thu, 27 Sep 2018 12:28:48 GMT

Yes I'm not sure where the duplication is coming from. With only the ShiftPlannedEnd highlighted here I would expect to see 8.11K instances of "9/27/2015 10:30:00 PM" but we only see one.

Re: Left Join + IntervalMatch causing count() to be incorrect?

swuehl — Thu, 27 Sep 2018 12:37:53 GMT

Well, I haven't understood your last sample and where the log.ShiftPlannedStart is located vs ShiftPlannedStart.

As far as I see, the duplicates in your shift table will cause the incorrect count after Joining the tables (as long as you keep them unjoined, a count in the log table will be correct, no duplication of records due to the JOIN).

--> Check your shifts table, consider using a DISTINCT LOAD of shifts.

Re: Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Thu, 27 Sep 2018 14:16:49 GMT

I apologize for the confusion. My actual "log" table has a slight different name so I had modified the the KPI name to match our discussion. The correct way it would be displayed with my script in this post is Count(log.ShiftPlannedStart).

So even if there are duplicates in a column the filter will only show one of those instances? I guess that does make sense.

I understand the concept behind using a distinct load but am unsure the extend of where I should use it. With my current understanding I've modified the script as follows...

[Shifts]:
Load Distinct
ShiftPlannedStart,
    ShiftPlannedEnd,
    Replace(Replace(Replace(Replace(Replace([ShiftName],'A','1'),'B','2'),'C','3'),'D','4'),'E','5') as [Shift];
SQL Select
ShiftPlannedStart,
    ShiftPlannedEnd,
    ShiftName
FROM Plant.dbo.Info;

Temp:
Left Join (log)
IntervalMatch("Timestamp")
Load ShiftPlannedStart, ShiftPlannedEnd
Resident Shifts;

Left Join (log)
Load *
Resident Shifts;

Drop Table Shifts;

Please let me know if this is what you intended.

The following modification has now given me the correct count when using the Dimension Log.Timestamp.Date and Measure Count(Text) on a bar graph.

The only thing I'm still unclear of is, why when counting log.ShiftPlannedStart do I get more than one instance?

Re: Left Join + IntervalMatch causing count() to be incorrect?

swuehl — Thu, 27 Sep 2018 14:57:05 GMT

This is after joining the shifts to your log?

Now you've get a ShiftPlannedStart record for each timestamp, so I guess you have 16 log events in that shift?

And yes, the filter pane does only show distinct values / symbols, not an entry per record.

Re: Left Join + IntervalMatch causing count() to be incorrect?

t-gilchrist — Thu, 27 Sep 2018 16:01:33 GMT

Yes, I think I understand now

Even though I am doing a distinct load for "Shift" when I eventually join it with "log" it attaches a plannedshiftstart and plannedshiftend to every event that is applies to. which means the Count for plannedshiftstart should equal the number of events inside that range. I.E why the Count(log.PlannedShiftStart) equals 16 (the number of events during that time span)

I believe everything is working perfectly now, thank you for your help. I will mark your answer as correct.