Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andreip21
Contributor III
Contributor III

divide a column in script based on proportionality of other column

Hi,

i have 2 separate tables (order id + p id + Sales and order id + p id + marketing cost)

how can i split the last column Sales into 2 parts (direct proportional) based on marketing cost (table 2)?

and the result to be (in my example) 25 and 75.

i want to try this in load script. thank you!

order idp idSales
110100

 

order idp idmarketing costSales
1102025
1106075
1 Solution

Accepted Solutions
petter
Partner
Partner

Like this:

COSTS:
LOAD * INLINE [
order id,p id,marketing cost
1,10,20
1,10,60
];

ORDER: LOAD * INLINE [
order id,p id,Sales
1,10,100
];

LEFT JOIN (ORDER)
LOAD
  [order id],
  [p id],
  Sum([marketing cost]) AS sum_marketing_cost
RESIDENT
  COSTS
GROUP BY
  [order id],[p id];
LEFT JOIN 
LOAD * RESIDENT COSTS;

DROP TABLE COSTS;

RESULT:
LOAD
  [order id],
  [p id],
  [marketing cost],
  [marketing cost]/sum_marketing_cost*Sales AS Sales
RESIDENT 
  ORDER;

DROP TABLE ORDER;

View solution in original post

3 Replies
petter
Partner
Partner

Like this:

COSTS:
LOAD * INLINE [
order id,p id,marketing cost
1,10,20
1,10,60
];

ORDER: LOAD * INLINE [
order id,p id,Sales
1,10,100
];

LEFT JOIN (ORDER)
LOAD
  [order id],
  [p id],
  Sum([marketing cost]) AS sum_marketing_cost
RESIDENT
  COSTS
GROUP BY
  [order id],[p id];
LEFT JOIN 
LOAD * RESIDENT COSTS;

DROP TABLE COSTS;

RESULT:
LOAD
  [order id],
  [p id],
  [marketing cost],
  [marketing cost]/sum_marketing_cost*Sales AS Sales
RESIDENT 
  ORDER;

DROP TABLE ORDER;

View solution in original post

andreip21
Contributor III
Contributor III
Author

hi, good enough, but i need one more thing.

 

for some order id x product id, i do not have marketing cost.

 

in the left join i am losing the Sales for these type of orders. how can i keep them?

 

Best,

Andrei

petter
Partner
Partner

Just add an extra line to keep the Sales in the last load statement:

 

 LOAD
     ......
     Sales AS OriginalSales,
    ......
;