Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Cathalc
Contributor III
Contributor III

Master table and canonical tables don't do their job

Hi dear Qlik people!

I've been working on a project where I can set out the flight hours of our planes and the defects that occurred on one timeline.

After a lot of trying I finally got to this point:

qlikquestion.png

Here's the problem: When I generate the table for the datelink I get a nice table in the form of:

Date                          RegName            DateType

01/01/2011             a                              test

02/01/2011             b                               FlightData

Which is nice. But when I plot the values of test and FlightData, the whole thing fails. The dates where nothing happened (no flight hours and no defects) the table fills up itself with total nonsense. the flighthours start counting up until a day passes where I do have flighthours and the same happens with the number of defects.

 

Has anyone had a similar problem? Or does anyone know where this problem comes from?

for the sake of completeness, here is m code:

LIB CONNECT TO 'RALNHV (nhv_qlikadmin)';
FactPIREPMAREP:
LOAD *;
SQL select 
    CreatedDate,
    tReg.Reg AS RegistrationName,
    OrderTaskType
from sOrderTask 
left JOIN sOrderTaskType on sOrderTaskType_ID = sOrderTaskType.ID
left join tATA on tATA.ID = tATA_ID
left join sOrder on sOrder_ID = sOrder.ID
left join tReg on tReg_ID = tReg.ID
where sOrderTaskType_ID = 2
or sOrderTaskType_ID = 3;


test:
load
	DayStart(Floor(Num(CreatedDate))) as CreatedDate,
	RegistrationName as RegName,
	OrderTaskType as PIREPMAREP
resident FactPIREPMAREP;
drop table FactPIREPMAREP;

CALL CalendarFromField('CreatedDate', 'Calendar', 'test');

FlightData:
Load	
	"Date" As FlightDate,
    %RegistrationId,
    %AircraftTypeId,
    #OpsMinutes,
    #TechMinutes,
    #Landings
FROM [lib://QVD-PRD/FactOperationalPerformance.qvd]
(qvd);

Join
	Load
		%RegistrationId,
    	RegistrationName as RegName
 FROM [lib://QVD-PRD/DimRegistration.qvd]
(qvd);
CALL CalendarFromField('FlightDate', 'FlightDataCalendar', 'FlightData.');

DateLink:
Load
	RegName,
	CreatedDate AS Date,
	'test' AS DateType
	Resident test;
    
Load
	RegName,
	FlightDate AS Date,
	'FlightData' AS DateType
	Resident FlightData;
    
CALL CalendarFromField('Date', 'Calendar', 'Datelink');

Thank you all! 

0 Replies