Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

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

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

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
Contributor II

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

Hiya

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

AllFields NOT FOUND

Please advise

sureshqv
Esteemed Contributor III

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

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

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
Contributor II

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

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

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

Community Browser