Need Help

[suvechha_b]

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 ??

[#2]

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

[kouroshkarimi]

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.

[Jason_Michaelides]

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

[suvechha_b]

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.......

[Jason_Michaelides]

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".

[#7]

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

[suvechha_b]

I am uploading my model please help me ...

Thanks,

[Jason_Michaelides]

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

  ];

[suvechha_b]

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)

  ];