Need help associating data by hour when 1 table doesn't have a full 24 hours

[OdieW]

I am working on an app showing planned values vs actual values. Planned values come from a form that is ingested into qlik. Actual values come from a company system.

I'm trying to show a table with columns:
Hour - Showing all 24 hours no matter what

Planned - Number of employees * how much work they should do per hour and then some logic for break and lunch hours

Actual - Actual work units done

Actual Head Count - Count of employees doing the work

Actual units per hour - Work done divided by count of employees.

My attempts to join the 2 tables seems to always cause loss of hours because the system won't always have work for every hour of the day for a specific activity. When I lose those hours I can't properly calculate hourly goal for all hours or a shift goal.

I found an option that might work but its not great and has an issue. I have an associated copy of the form data and then another copy of the form data that is qualified and not associated so I don't lose hours and can do shift goals. But it's not associated so it doesn't work with the Hour column used in the table.

I basically just need a way to associate the form and system data but not lose any hours. It's fine if when a system hour is missing if it reports as null or 0 i just need the hour to show so I can also show planned numbers for that hour.

Thanks!

[Sayed_Mannan]

This is happening because Actual data does not contain all 24 hours, so when you join it with Planned data, Qlik only keeps hours that exist in both tables, causing missing hours and breaking you calculations.

To avoid losing hours, create a master 0-23 hour table and left join both planned and actual data to it, this guarantees all hours show, and missing Actual values just appear as null/0.

You can do something like this:

Hours:

LOAD RowNo() - 1 AS Hour AUTOGENERATE 24;

then you can left join it with Planned and Actual data. this should work.

[marcus_sommer]

Fact-tables couldn't be linked if there are missing key-values on any side - at least not without loosing some information. To avoid this issue you could check both tables against each other and populating the missing information.

Simpler as above would be to concatenate both facts into a single fact-table by harmonizing field-names and data-structures as much as possible. Both tables (actuals + plan) contain mainly the same information just the view-direction is opposite. Some differences in extra information and/or granularity are not a mandatory showstopper else solvable. Further avoids this approach all the trouble to associate the facts with further dimensions.   

[OdieW]

I already have an Hour master table and left join was my first thought as well, although I initially did a left join of system data to form data and that didn't work. I have now tried left joining both system and form data to the hour master table but still I seem to lose any hours not in the system data.

[Sayed_Mannan]

I agree with Marcus, you are still losing hours because LEFT JOIN always risk removing rows whenever the Hour values don't match exactly between you tables. Even if you have 24-hours master table, joining fact tables into it will rewrite the table and can drop hours.

You can try the another approach by doing this,

1. Keep your 24 hours master table as a separate dimension. Do not join facts into it. It should simply contain the number 0-23 and stand alone.

2. Combine(Concatenate) both Planned and Actual use the same Hour key. Extract the hours in the same way from both data sets so the associations match correctly.

3. Connect this single fact table to your Hour dimension.

This should work because when you have one unified fact table connected to a separate 24 hours dimension, Qlik will not drop hours and missing values just become nulls, but the hours stay visible.

Try this and let me know this works or not.

If this still doesn't work, share your actual field names, timestamp field, and the structure of your planned and actual tables so I can review your exact data model.

[OdieW]

Just so I am clear when you say "Extract the hours in the same way from both data sets so the associations match correctly." what do you mean?

Currently, my planned data has hours 0-23 on their own without requiring any manipulation to get them. Actual data I am pulling hour from a timestamp with the Hour() function. I definitely want to give this a try...

But I should also let you know I am now attempting to move this (Planned data) to write table inside Qlik instead of an external form. Doing this for 2 reasons, the form solution as it was wasn't very flexible to add hours to a shift for example. I also just think it makes sense to stay inside qlik if/when I can.

Currently my potential write table solution does away with the full 0-23 hours and instead requires shift start, break 1, lunch, break 2 and shift end hours. I should then be able to auto generate whatever hours between shift start and shift end, which if its a normal 3 shifts then I would end up with 0-23 hours across all 3. More likely its probably only first and second shift and as I mentioned shifts could expand from 8 to say 10 hours as needed, but again I should be able to auto generate all the shift hours from start to end and relate them to the hour master table so I think this should still work with your idea.

Update: I concatenated the planned and actual tables and I can see all hours when unfiltered. But when I filter to today it only shows me 12 AM through 1 PM. I assume because that's all the hours that have passed or started today. My planned data does not have a date field whereas the actual data does have a date field. So I assume now it's filter them out because of the date not because they aren't in the data.

For the Hour Master table do I need 2 fields, an hour/key to join on and then another hour that woudl be the actual hour to display in charts?

Update 2: I messed up and had some actual fields commented out, let me fix this and report back again.

[marcus_sommer]

I think I would use definition-tables with a main-structure like:

Location	Shift	StartDate	EndDate	StartTime	EndTime	TimeCategory	DateType
a	1					Work	Workday
b	2					Break	Weekend
c	3						Holiday
							SpecialDay

and then using internal while-loops to expand these information to tables which contain a dedicated date + dedicated time together with the various mapping/matching-information as well as n running/accumulated date/time data. Here an example to the main-logic:

t: load *, applymap('m', Date, '#NV') as DateType;
   load *, date(StartDate + iterno() - 1) as Date
   from Source while StartDate + iterno() - 1 <= EndDate;

Afterwards this kind of table is used as basic-fact to which the actual/planned production-data are joined/mapped and/or itself used as source for n joinings/mappings and/or as dimension-information.

The aim is to provide all essential information as native data which could be simply picked and/or used as dimensions/selections by using simple expressions like: sum(WorkingMinutes).

It's much simpler as trying to get the relevant information with any conditional approaches querying a record if it belonged to a certain condition/requirement. Especially if there are any changes to the shifts and/or n different definitions in regard to a location or similar and/or more difficult if the shifts could be overlapping each other it becomes quite complex to fetch everything with a conditional logic.

Further if this kind of data needs to be combined with n orders/contracts and/or n machine-runtimes which span over n dates/shifts and/or individual employee-information (not all are FTE, might be ill, are too late, ...) it would be a nightmare of trying conditional approaches. Therefore the above would be my preferred starting point.