Sales by Customer and zip code

[wanyunyang]

Hi guys,

I have customer table and sales table as below:

customer:
load * Inline
[
SalesRep, Customer, ZipCode, County
M, A, 00011, NY
M, A, 00022, NY
M, A, 00044, NY
M, A, 00055, CA
N, A, 00088, CA
];

sales:
load * Inline
[
Customer, Sales
A, 500
];

The result is like:

So customer A has five stores, with total sales 500. Is it possible to change it from script, so that each of these five store could have 100 sales, with total sales 500?

I would like to change this from script rather than layout, since my real data is big and have complex sales function, which would take too long time if using aggr() from layout.

Thanks in advance!

[dplr-rn]

in my opinion This more of a data problem than a qlik problem.
i am not sure makes sense to divide sales equally between the stores.

[Vegar · Accepted solution]

Try the script below.

TmpSales:
load * Inline
[
Customer, Sales
A, 500
];
Left join (TmpSales)
load * Inline
[
SalesRep, Customer, ZipCode, County
M, A, 00011, NY
M, A, 00022, NY
M, A, 00044, NY
M, A, 00055, CA
N, A, 00088, CA
];
Left join (TmpSales)
Load
Customer,
count(ZipCode) as NoOfCust
Resident TmpSales;
Group by Customer
;
Sales:
Load Customer,
Sales/NoOfCust as Sales
Resident TmpSales;
Drop table sales;

[Channa]

sales:
load * Inline
[
Customer, sum(Sales)/count(Customer) resident customer
group by customer
];

[wanyunyang]

I got your idea. Thanks!