topic Condition WHERE after Joining 2 QVDs in Data Load Editor in App Development

Condition WHERE after Joining 2 QVDs in Data Load Editor

Sergiu — Tue, 11 Apr 2023 09:13:52 GMT

I have joined 2 QVDs , and then loaded a new Table "Calculated_Table" where after the join i am substracting 2 dates

Any idea why is not allowing me to put a WHERE condition in the "Calculated Table" after the IF statement ?

PO_RECEIVED_ORIGIN:
LOAD
FCM_PO_OBJECT_ID,
CUSTOMER_NAME as PO_RECEIVED_ORIGIN_CustomerName,
VENDOR_NAME as PO_RECEIVED_ORIGIN_VendorName,
CONSIGNEE_NAME as PO_RECEIVED_ORIGIN_ConsigneeName,
PO_DATE as PO_RECEIVED_ORIGIN_PODate,
PO_NUMBER as PO_RECEIVED_ORIGIN_PONumber,
MODE_OF_TRANSPORT_DESC as PO_RECEIEVD_ORIGIN_Mode,
ADDITIONAL_DATE_1 as PO_RECEIVED_ORIGIN_ETA

FROM [QVD1]
(qvd);

INNER JOIN (PO_RECEIVED_ORIGIN)
LOAD
FCM_PO_OBJECT_ID,
STATUS_CODE as STATUS_RECEIVED_ORIGIN_StatusCode,
STATUS_DESC as STATUS_RECEIVED_ORIGIN_StatusDesc,
STATUS_DATE as STATUS_RECEIVED_ORIGIN_StatusDate

FROM [QVD2]
(qvd)
where "STATUS_CODE"='TAL03';

Calculated_Table:
LOAD
FCM_PO_OBJECT_ID,
PO_RECEIVED_ORIGIN_PONumber,
PO_RECEIVED_ORIGIN_ETA,
STATUS_RECEIVED_ORIGIN_StatusCode,
STATUS_RECEIVED_ORIGIN_StatusDate,
IF(ROUND(PO_RECEIVED_ORIGIN_ETA - STATUS_RECEIVED_ORIGIN_StatusDate)<=7,'At Risk','Not At Risk') as Flag

where FLAG = 'At Risk'; ##this line breaks the code , have tried to put it after resident, after drop table nothing works

Resident PO_RECEIVED_ORIGIN;
Drop Table PO_RECEIVED_ORIGIN;

Re: Condition WHERE after Joining 2 QVDs in Data Load Editor

BrunPierre — Tue, 11 Apr 2023 09:50:16 GMT

NoConcatenate

LOAD *

Where FLAG = 'At Risk';

Resident PO_RECEIVED_ORIGIN;

Drop Table PO_RECEIVED_ORIGIN;

Re: Condition WHERE after Joining 2 QVDs in Data Load Editor

Sergiu — Tue, 11 Apr 2023 09:44:26 GMT

Thanks! but it cannot recognize the calculated field FLAG

This is the error while loading the data :

"The following error occurred:

Field 'Flag' not found"

Re: Condition WHERE after Joining 2 QVDs in Data Load Editor

BrunPierre — Tue, 11 Apr 2023 09:54:18 GMT

Try again with minor changes.

NoConcatenate

LOAD *

Where FLAG = 'At Risk';

Calculated_Table:
LOAD
FCM_PO_OBJECT_ID,
PO_RECEIVED_ORIGIN_PONumber,
PO_RECEIVED_ORIGIN_ETA,
STATUS_RECEIVED_ORIGIN_StatusCode,
STATUS_RECEIVED_ORIGIN_StatusDate,
IF(ROUND(PO_RECEIVED_ORIGIN_ETA - STATUS_RECEIVED_ORIGIN_StatusDate)<=7,'At Risk','Not At Risk') as Flag

Resident PO_RECEIVED_ORIGIN;

Drop Table PO_RECEIVED_ORIGIN;

Re: Condition WHERE after Joining 2 QVDs in Data Load Editor

Sergiu — Tue, 11 Apr 2023 12:20:30 GMT

Thanks a lot ! 🙂 it worked