topic Re: Need Help in QlikView

Need Help

suvechha_b — Fri, 15 Nov 2013 10:22:56 GMT

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

Re: Need Help

Fri, 15 Nov 2013 10:27:20 GMT

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

Re: Need Help

kouroshkarimi — Fri, 15 Nov 2013 10:34:39 GMT

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.

Re: Need Help

Jason_Michaelides — Fri, 15 Nov 2013 10:57:34 GMT

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

Re: Need Help

suvechha_b — Fri, 15 Nov 2013 11:29:46 GMT

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

Re: Need Help

Jason_Michaelides — Fri, 15 Nov 2013 11:39:59 GMT

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

Re: Need Help

Fri, 15 Nov 2013 12:56:59 GMT

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

Re: Re: Need Help

suvechha_b — Fri, 15 Nov 2013 13:01:39 GMT

I am uploading my model please help me ...

Thanks,

Re: Need Help

Jason_Michaelides — Fri, 15 Nov 2013 13:15:01 GMT

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

];

Re: Re: Need Help

suvechha_b — Fri, 15 Nov 2013 13:29:03 GMT

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)

];

Re: Need Help

Jason_Michaelides — Fri, 15 Nov 2013 13:33:15 GMT

Remove the $ symbols and test - still get the error?

Re: Re: Re: Need Help

suvechha_b — Fri, 15 Nov 2013 13:38:44 GMT

please see the attachment..

Re: Need Help

Jason_Michaelides — Fri, 15 Nov 2013 13:40:34 GMT

Attach your qvw again please.

Re: Re: Need Help

suvechha_b — Fri, 15 Nov 2013 13:58:48 GMT

PFA

Re: Need Help

Jason_Michaelides — Fri, 15 Nov 2013 14:15:47 GMT

$(vRisks) is expanding within the script to your Aggr() function. This has square brackets around one or more field names - replace these with double-quotes and you should be good. The closing square bracket ] is closing the Inline table too early.

Check $(vUnit) is not doing the same.