Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to write in expression :
My expression look like this :
if(name= 'Opening Stock On Hand', sum(opening_stock_unit),
if(name= 'Budget Recommended Order',sum(budget_recommended_order_quantity),
if(name= 'Recommended Order',sum(recommended_order),
if(name= 'Inbound Orders',sum(inbound_orders),
if(name= 'Actual Receipts', sum(purchase_receipts),
if(name= 'Adherence to plan(Factory)%',(sum(purchase_receipts)/sum(inbound_orders)*100),
if(name= 'Sales Budget Units',sum(sales_budget_units),
if(name= 'Sales Plan(Planner Forecast)',sum(sales_plan),
if(name= 'Actual Sales', sum(sale_units),
if(name= 'Customer Orders',sum(customer_order),
if(name= 'Adherence to Plan(MAPE3)%',RangeMax(0,100-fabs(sum(error))/Sum([actual])*100),
if(name= 'Adherence to Plan(ARITH1)%',(Sum([Actual Units])/Sum([Forecast Units])*100),
if(name= 'Model Stock Units', sum(model_stock_units),
if(name= 'Days On Hand',sum(days_on_hand),
if(name= 'Projected Adjustment',Sum(projected_adjustment_unit),
if(name= 'Closing Stock On Hand',sum([closing_stock_unit]),
if(name= 'Opening Stock on Hand Horizon Inbound Orders',Sum(OpeningStockOnHandHorizon),
if(name= 'Full Horizon Inbound Orders',Sum(FullHorizonInbounds),
if(name= 'Closing Stock on Hand Horizon Inbound Orders', Sum(ClosingStockOnHandHorizon),
if(name= 'RISK',RangeMin(0, $(vRisks)-$(vUnit)),Sum(qa_blocked_stock)))))))))))))))))))))
the RISK is not calculating .
how to get it right ??
Hi
i wouldn't say it is the best way to make it.
Why can't you store your different expression under a unique name in the script.
Use a mapping table and according to name you store the right field in a field (let say : sum_for_name) and then you sum(sum_for_name) in your expression
best regards
chris
Wow. Just... wow. You should think about re-working that.
Have you tried breaking it down to find which part is not working? Put RangeMin(0, $(vRisks)-$(vUnit)) into a text object to see if it actually gives you an answer. If not, then try putting the individual variables in an object.
You might need extra parentheses in there:
RangeMin(0, ($(vRisks)-$(vUnit)))
However, you should definitely replace that expression with a much more efficient one. One way would be to load an association between <name> and the expression as Chris suggests. Maybe like this:
Map_NameToMeasure:
MAPPING LOAD * INLINE [
name,Expression
Opening Stock On Hand,sum(opening_stock_unit)
Budget Recommended Order,sum(budget_recommended_order_quantity)
Recommended Order,sum(recommended_order)
Inbound Orders,sum(inbound_orders)
Actual Receipts,sum(purchase_receipts)
Adherence to plan(Factory)%,(sum(purchase_receipts)/sum(inbound_orders)*100)
Sales Budget Units,sum(sales_budget_units)
Sales Plan(Planner Forecast),sum(sales_plan)
Actual Sales,sum(sale_units)
Customer Orders,sum(customer_order)
Adherence to Plan(MAPE3)%,RangeMax(0,100-fabs(sum(error))/Sum([actual])*100)
Adherence to Plan(ARITH1)%,(Sum([Actual Units])/Sum([Forecast Units])*100)
Model Stock Units,sum(model_stock_units)
Days On Hand,sum(days_on_hand)
Projected Adjustment,Sum(projected_adjustment_unit)
Closing Stock On Hand,sum([closing_stock_unit])
Opening Stock on Hand Horizon Inbound Orders,Sum(OpeningStockOnHandHorizon)
Full Horizon Inbound Orders,Sum(FullHorizonInbounds)
Closing Stock on Hand Horizon Inbound Orders, Sum(ClosingStockOnHandHorizon)
Risk,RangeMin(0, ($(vRisks)-$(vUnit)))
];
TableWithNameInIt:
LOAD
*
,ApplyMap('Map_NameToMeasure',name,'<Unknown>') AS Expression
FROM...;
Then, your expression above could be something like:
IF(GetPossibleCount(Expression)=1,$(Expression),Sum(qa_blocked_stock))
Hope this helps,
Jason
As I am new to qlikview....I havent use ApplyMap before.
Can you please describe how to do this part step by step details :
TableWithNameInIt:
LOAD
*
,ApplyMap('Map_NameToMeasure',name,'<Unknown>') AS Expression
FROM...;
Then, your expression above could be something like:
IF(GetPossibleCount(Expression)=1,$(Expression),Sum(qa_blocked_stock))
Thanks in Advance.......
At some point in your script you are loading a table with a field called "name" . In that load script, insert the ApplyMap() line I've provided above. This will add a new field called "Expression".
First you have to create a mapping table
Map_Table:
MAPPING LOAD distinct
field_A, // means field value in
field_B // means field value out
FROM ....
Table:
LOAD
...
applymap('Map_Table', field_A, default_value) as field_you_want // Field _A will be returned with field_B // value
FROM ...
Hope it is clear enough.
You can use QlikView help as well
Chris
I am uploading my model please help me ...
Thanks,
OK - you're using an INLINE table anyway so no need for ApplyMap(). Just add the Expression field to your Inline table:
//Group:
LOAD * INLINE [
Group,name ,Expression
SupplyPlan,Opening Stock On Hand,sum(opening_stock_unit)
SupplyPlan,Budget Recommended Order,sum(budget_recommended_order_quantity)
SupplyPlan,Recommended Order,sum(recommended_order)
SupplyPlan,Inbound Orders,sum(inbound_orders)
SupplyPlan,Actual Receipts,sum(purchase_receipts)
SupplyPlan,Adherence to plan(Factory)%,(sum(purchase_receipts)/sum(inbound_orders)*100)
SalesPlan,Sales Budget Units,sum(sales_budget_units)
SalesPlan,Sales Plan(Planner Forecast),sum(sales_plan)
SalesPlan,Actual Sales,sum(sale_units),
SalesPlan,Customer Orders,sum(customer_order)
SalesPlan,Adherence to Plan(MAPE3)%,RangeMax(0,100-fabs(sum(error))/Sum(actual)*100)
SalesPlan,Adherence to Plan(ARITH1)%,(Sum("Actual Units")/Sum("Forecast Units")*100)
SalesPlan,Model Stock Units,sum(model_stock_units)
Inventory,Days On Hand,sum(days_on_hand)
Inventory,Projected Adjustment,Sum(projected_adjustment_unit)
Inventory,Closing Stock On Hand,sum("closing_stock_unit")
Inventory,Opening Stock on Hand Horizon Inbound Orders,Sum(OpeningStockOnHandHorizon)
Inventory,Full Horizon Inbound Orders,Sum(FullHorizonInbounds)
Inventory,Closing Stock on Hand Horizon Inbound Orders,Sum(ClosingStockOnHandHorizon)
Inventory,RISK,RangeMin(0, ($(vRisks)-$(vUnit)))
Inventory,Blocked Stock
];
I am getting an error : Garbage value after this:
LOAD * INLINE [
Group,name,Expr
SupplyPlan,Opening Stock On Hand,sum(opening_stock_unit)
SupplyPlan,Budget Recommended Order,sum(budget_recommended_order_quantity)
SupplyPlan,Recommended Order,sum(recommended_order)
SupplyPlan,Inbound Orders,sum(inbound_orders)
SupplyPlan,Actual Receipts,sum(purchase_receipts)
SupplyPlan,Adherence to plan(Factory)%,(sum(purchase_receipts)/sum(inbound_orders)*100)
SalesPlan,Sales Budget Units,sum(sales_budget_units)
SalesPlan,Sales Plan(Planner Forecast),sum(sales_plan)
SalesPlan,Actual Sales,sum(sale_units)
SalesPlan,Customer Orders,sum(customer_order)
SalesPlan,Adherence to Plan(MAPE3)%,$(vAE)
SalesPlan,Adherence to Plan(ARITH1)%,(Sum(Actual Units)/Sum(Forecast Units)*100)
SalesPlan,Model Stock Units,sum(model_stock_units)
Inventory,Days On Hand,sum(days_on_hand)
Inventory,Projected Adjustment,Sum(projected_adjustment_unit)
Inventory,Closing Stock On Hand ,sum(closing_stock_unit)
Inventory,Opening Stock on Hand Horizon Inbound Orders,Sum(OpeningStockOnHandHorizon)
Inventory,Full Horizon Inbound Orders,Sum(FullHorizonInbounds)
Inventory,Closing Stock on Hand Horizon Inbound Orders, Sum(ClosingStockOnHandHorizon)
Inventory,RISK,RangeMin(0, ($(vRisks)-$(vUnit)))
Inventory,Blocked Stock,sum(qa_blocked_stock)
];