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

[t-gilchrist]

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;

[swuehl · Accepted solution]

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.

[t-gilchrist]

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?

[swuehl]

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.

[t-gilchrist]

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.