Left join load with 'where' clause error

[cawestelaken]

Hello,

I've got the following load statement which is throwing me the error: Field '%Date_key' not found.

Does anyone know why and how to fix this?

Facts:
NoConcatenate LOAD
%Date_key,
%Incident_key,
%Employee_key,
Type,
FROM
[lib://ITSM/FCT_ITSMUren.qvd]
(qvd)
where year(%Date_key) >= '2014'
and Type = 'Incident';

Left Join LOAD
distinct %Incident_key,
1 AS _Flag_IncidentNieuw
Resident Incident
where match(%Date_key, date(Floor(Incident.SysCreated), 'DD-MM-YYYY'));

Yours sincerely,

Casper Westelaken

[sunny_talwar]

Does your Incident field have %Date_key field in it?

[jensmunnichs · Accepted solution]

I think you need Exists() instead of Match(). Exists can look at previously loaded tables and I don't think Match can do that.

So it would be:
where Exists(%Date_key, date(Floor(Incident.SysCreated), 'DD-MM-YYYY'));

More info on Exists:
https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

[cawestelaken]

The Incident table does not have a %Date_key in it. This'd result in a Synthetic Key which I'm trying to avoid.

I was hoping that it'd take the %Date_key from the previously loaded Facts table

[sunny_talwar]

For Where statement to work the way it is coded, you would need it Incident table to have %Date_key... you can drop this field after it serves it purpose within the Left Join table.

DROP FIELD %Date_key from Incident;

[cawestelaken]

@jensmunnichs

This is the correct solution apart from an error in my datasets, which results in this not giving the correct result (luckily this is fixable.)

Thank you very much!

[cawestelaken]

@sunny_talwar

Whilst this does work, this does result in more calculations that need to be done. The other solution is therefore better.

Thank you very much for your help!

[sunny_talwar]

Glad you found the solution you were looking for.