Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joeybird
Creator III
Creator III

If statement across two tables and using MIN /MAX function

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

6 Replies
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
Creator III
Creator III
Author

Hiya

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

AllFields NOT FOUND

Please advise

Chanty4u
MVP
MVP

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
Creator III
Creator III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!