topic Re: If statement across two tables and using MIN /MAX function in App Development

If statement across two tables and using MIN /MAX function

joeybird — Wed, 17 Feb 2016 15:53:45 GMT

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

Re: If statement across two tables and using MIN /MAX function

sunny_talwar — Wed, 17 Feb 2016 15:56:03 GMT

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;

Re: If statement across two tables and using MIN /MAX function

joeybird — Thu, 18 Feb 2016 10:27:58 GMT

Hiya

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

AllFields NOT FOUND

Please advise

Re: If statement across two tables and using MIN /MAX function

Chanty4u — Thu, 18 Feb 2016 10:32:07 GMT

did u try till dis code?

load *,

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

resident Orders;

Re: If statement across two tables and using MIN /MAX function

sunny_talwar — Thu, 18 Feb 2016 10:38:09 GMT

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

Re: If statement across two tables and using MIN /MAX function

joeybird — Thu, 18 Feb 2016 12:29:21 GMT

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

Re: If statement across two tables and using MIN /MAX function

kaushiknsolanki — Thu, 18 Feb 2016 12:32:08 GMT

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