Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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 FlagResident PO_RECEIVED_ORIGIN;
Drop Table PO_RECEIVED_ORIGIN;
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;
Thanks! but it cannot recognize the calculated field FLAG
This is the error while loading the data :
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 FlagResident PO_RECEIVED_ORIGIN;
Drop Table PO_RECEIVED_ORIGIN;
Thanks a lot ! 🙂 it worked