Hi all!
For my company I'm trying to develop a script so I can divide the number of defects by the flight hours for our planes. The problem is that the flight hours and defects are logged with different dates. This is why I'm trying to create a canoncial date table. The next picture is the layout of my situation:
The next step in my script is to create a bridgetable as described in this tutorial: https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578
The problem is that I have a table 'AcRegistration' which i need to connect with the two tables 'FlightHours' and FactPIREPMAREP. It's an intermittent step that is not the case in the tutorial that i'm following.
I've got the following script to start from:
AcTypeId2CreatedDate:
MAPPING LOAD
AcTypeId,
RegName
RESIDENT FlightHours;
DateBridge:
Load
AcTypeId,
Applymap('AcTypeId2CreatedDate',AcTypeId,Null()) as Date,
'Required' as DateType
Resident FlightHours;
Load
AcTypeId,
FlightDate as Date,
'Flight' as DateType
Resident FlightHours;
The problem is that I have absolutely no idea how to further develop the mapping script in order to create the bridgetable. I would have to use a second mapping function to link 'RegName' with 'Created Date', but i'm already having errors with AcTypeId2CreatedDate. Does anyone have suggestions?