Discussion board where members can get started with QlikView.
I need to load, by customer, the last date a product was ordered. The below script works fine, but obviously the max(Date) and Group By options are commented out.
LatestOrders:NOCONCATENATE LOADCustCode,//Max(Date) as MaxDate,Date,Order,OrderStatusH,OrderInvStatusD,ProductCode,OrderSentqtyD,ActCostD,ActWsvD,ActRsvD,TaholaDateOrderRESIDENT TempLatestOrderDetails;
//GROUP BY CustCode, ProductCode;
STORE LatestOrders INTO \\bi\Data\QVD\LatestOrders.qvd (QVD);
DROP Table TempLatestOrderDetails;
If I change the script to include the max(Date) and Group By I get an INVALID EXPRESSION error:
LatestOrders:NOCONCATENATE LOADCustCode,Max(Date) as MaxDate,//Date,Order,OrderStatusH,OrderInvStatusD,ProductCode,OrderSentqtyD,ActCostD,ActWsvD,ActRsvD,TaholaDateOrderRESIDENT TempLatestOrderDetails
GROUP BY CustCode, ProductCode;
I'm missing something obvious ....but can't work it out. What am I doing wrong?
Many thanks for your help 😁
All the fields you load along with Max(Date) statement (in the same load statement) need to be there in Group By clause.
Max(Date) as Max,
F2, F3, .... Fn
from <> Group By F1, F2, F3, ....Fn; // You might want to change the field order here.
View solution in original post
when using group by the fields you load
need to be either in the group by part or
loaded inside an aggr functions for example maxstring , only
Ahhhh ...that makes sense now. Many thanks
I've simplified the script, and now it works perfectly:
LatestOrders:LOADCustCode,Max(Date) as MaxDate,ProductCodeRESIDENT TempLatestOrderDetails
GROUP BY ProductCode, CustCode;
Thanks again 👍