The checkings table can be broken down to three groups of check IDs. Here I will only show for group1, table checkings1. This checkings1 table involves different types of checkings for this group of checks (field “type” of table checkings1).
checkings:
LOAD
"ID",
date(floor("checkdate"),'DD/MM/YYYY') as checkdate,
date("checkdate",'DD/MM/YYYY hh:mm:ss.ffffff') as “checkdate timestamp”,
"check ID"
FROM [$(vPathQVD)/checking.qvd]
(qvd);
LEFT JOIN(checkings)
LOAD
"Result ",
"Type",
"check ID1" as "check ID ",
“check ID1”
FROM [$(vPathQVD)/checkings1.qvd]
(qvd);
LEFT JOIN (checkings)
LOAD
"ID ",
“Type",
Date(Max( "checkdate")) AS [last checkdate of checkings1]
, max("check ID") as maxcheck1_id
RESIDENT
checkings
GROUP BY
"ID",
"Type"
;
For Each vType In FieldValueList('Type')
[$(vType) Checking1 Details]:
LOAD
"ID”,
maxckeck1_id as [ID of last checking1 type $(vType)],
[result] as [last result of checking1 type $(vType)]
RESIDENT
checkings
WHERE
"Type" = '$(vType)'
AND [check ID] = maxcheck1_id
;
Next vType
DROP FIELD [last checkdate of checkings1] FROM checkings;
And then I have a master calendar on field “checkdate”
