If statement across two tables and using MIN /MAX function

[joeybird]

Hi

I have two sql tables, in which I am using

Orders:

Order_DateCheck

left join

Shipping:

Shipping_DateCheck

I am then trying to create a IF statement across the two SQL tables as a  flag to see if Max Shipping_DateCheck, is more than Order_DateCheck

load *,

if(max("Order_DateCheck")>("Shipping_DateCheck"),1,0) AS CustomerFlag

resident Orders;

keep getting error

Where am I going wrong?

Please help

[sunny_talwar]

Have you added a Group By Statement? Whenever you have Aggregation (Max()) you need a Group By statement:

load *,

if(max("Order_DateCheck")>("Shipping_DateCheck"),1,0) AS CustomerFlag

resident Orders

GROUP BY AllFields;

[joeybird]

Hiya

the statement is having issues with the group by, keeps having an error with

AllFields NOT FOUND

Please advise

[Chanty4u]

did u try   till dis code?

load *,

if(max("Order_DateCheck")>("Shipping_DateCheck"),1,0) AS CustomerFlag

resident Orders;

[sunny_talwar]

Hahahaha you are supposed to replace AllFields with the field names you are loading into the table. It was just a placeholder to guide you

[joeybird]

Hiya

I have

tried to add all column names and added to try the expression ones too

e.g if ...... as "CustomerFlag"

load *,

if(max("Order_DateCheck")>("Shipping_DateCheck"),1,0) AS CustomerFlag

resident Orders

GROUP BY Order_DateCheck, Shipping_DateCheck, CustomerID, CustomerName,  ShippingID , ShipperName, CustomerFlag;

keep getting invalid expression

please help

[kaushiknsolanki]

Hi Try this way

Max:

Load Max(Order_DateCheck) as Max

From Order;

Let vDate = peek('Max',0,'Max');

load *,

if($(vDate)>("Shipping_DateCheck"),1,0) AS CustomerFlag

resident Shipment;

Regards,

Kaushik Solanki