topic Load Custom Values? in QlikView

Load Custom Values?

Fri, 04 Feb 2011 22:34:42 GMT

I would like to add a value a custom value (commission_cost) to my margin. The margin calculation is: sum(extended_price-(commission_cost*qty_shipped)). Basically the application/DB QV is reading from does not allow us to enter a value for this particular order type but we still want it to count towards our margin. I do not want to edit the DB to add in these values manually. So I wondering if QV could 'add' this custom value and associate it correctly. Is this possible?

It needs to be associated with commission_cost with order_no (to link it up), extended_price, qty_shipped all needed to calculate. Order_no, commission_cost, extended_price and qty_shipped all pulled from the same table (TotalSales). I believe this should help. I think a resident load would work but I unsure of syntax linkage. It usually ends up breaking something else or creating lots of ugly sync tables. So I must be doing something wrong.

This is what i tried:


CustomCommission:
LOAD * INLINE [
order_no, commission_cost
9052813 , 6000];
CustomExtended_price:
LOAD * INLINE [
order_no, extended_price
9052813 , 0];
CustomQty_Shipped:
LOAD * INLINE [
order_no, qty_shipped
9052813 , 0];

This works but I dislike having to add it to the margin:



CustomCommission:
LOAD order_no,
 commission_cost AS com_co
FROM
C:\commission.xlsx
(ooxml, embedded labels, table is Com);
//then I use this to add it to the margin in my pivot table
sum(extended_price-(commission_cost*qty_shipped)))+sum(com_co)

So I would like my logic to be: (I do not think this exists but ideal...)

IF(order_no = '9052813') THEN extended_price = '0' , commission_cost = '6000', qty_shipped = '0'

RESIDENT Total Sales:

Thanks for any assistance you can offer. This solution may be easier than the approach I am taking if so let me know. Thanks once again.

AW:Load Custom Values?

Sat, 05 Feb 2011 17:19:09 GMT

Hello Jacob,

if I got you right, you should try the following reload-code snippet:

//Input from DB-Table TotalSales:

 TotalSales:
 LOAD * INLINE [
 order_no, commission_cost
 9052813 , 6000];

 Join(TotalSales)
 LOAD * INLINE [
 order_no, extended_price
 9052813 , 0];

 Join(TotalSales)
 LOAD * INLINE [
 order_no, qty_shipped
 9052813 , 0];
//


// Now QV-Join com_co from excel to DB-data:
// Hope that order_no is a unique field
Join(TotalSales)
LOAD * Inline [
order_no, com_co
9052813 , 10];

//
//sum(extended_price-(commission_cost*qty_shipped)))+sum(com_co)
//now do the calc as you like during resident load
//
Join(TotalSales)
LOAD
 *,
 extended_price-(commission_cost*qty_shipped) + com_co AS SomeThing
Resident TotalSales;

HtH

Roland

AW:Load Custom Values?

Mon, 07 Feb 2011 17:42:10 GMT

I like the logic. The Resident load correctly loading is correct. I would like the sum(commission_cost + com_co) = new_commission_cost. Then in all my other equations I can just update old commission_cost with 'new_commission_cost' but it is still loading all the commission_costs...

I will play some more and post back. Thanks.