topic Re: Mapping sales based on two variables in QlikView

Mapping sales based on two variables

Thu, 27 Jun 2013 06:25:38 GMT

Hope someone can assist as I try to link sales based on two different variables (post code and division) to one sales person.

Post Code	Accessoires	Shoes	Equipment	Clothing
1680	Les	Paul	Les	Allan
1685	Sarah	Clyde	Paul	Paul
1700	Clyde	Sarah	Allan	Les
1701	Paul	Les	Rene	Sarah
1710	Sarah	Rene	Sarah	Rene

So if a sale is reported on postcode '1680' and 'shoes' the sales should be aligned to Paul. If the combination was '1680' and 'Clothing', then the sales should be aligned to Allan?

Any suggestions on how to write the script?

Thanks in advance.

Re: Mapping sales based on two variables

Sokkorn — Thu, 27 Jun 2013 06:35:58 GMT

Hi,

Can you give desire result from your table above?

Regards,

Sokkorn

Re: Mapping sales based on two variables

Thu, 27 Jun 2013 07:30:44 GMT

Visually it should appear in QlikView like below (excel) example:

Sum of Total Sales	Sales Rep
Product	Sarah	Paul	Rene	Allan	Les	Clyde	Grand Total
Accessoires	$12,845	$1,595		$6,375	$2,650	$12,085	$35,550
Clothing	$6,916	$20,774	$13,520	$29,068	$14,326	$18,174	$102,778
Shoes	$44,800	$119,325	$61,050	$49,825	$20,375	$8,225	$303,600
Equipment	$19,836	$118,978	$99,370	$12,008	$46,056	$8,664	$304,912
Grand Total	$84,397	$260,672	$173,940	$97,276	$83,407	$47,148	$746,840

Re: Mapping sales based on two variables

tresB — Thu, 27 Jun 2013 07:34:36 GMT

May be CROSSTABLE? try using this in the script.

Re: Mapping sales based on two variables

Thu, 27 Jun 2013 08:31:10 GMT

Hi Tresesco,

I don't the crosstable will solve the issue since the sales file doesn't have the Sales Rep in it. I've attached the sales file so you can see what I mean. With an additional table I want to link the right Rep to the Postcode and Product. It's a good way for you to earn some 10 extra points

Date	Customer	Post Code	Quantiy	Product	Unit Price	Total Sales
10/06/2013	J	2035	187	Shoes	$25	$4,675
22/06/2013	V	1680	493	Shoes	$25	$12,325
5/06/2013	E	1710	181	Clothing	$13	$2,353
21/06/2013	U	2032	87	Equipment	$38	$3,306
4/06/2013	D	1701	438	Shoes	$25	$10,950
11/06/2013	K	2075	86	Shoes	$25	$2,150
1/06/2013	A	1680	473	Shoes	$25	$11,825
24/06/2013	X	2076	71	Accessoires	$5	$355
6/06/2013	F	2031	354	Shoes	$25	$8,850
2/06/2013	B	1685	208	Clothing	$13	$2,704
12/06/2013	L	2076	169	Clothing	$13	$2,197
5/06/2013	E	1710	163	Shoes	$25	$4,075
20/06/2013	T	2031	477	Shoes	$25	$11,925
18/06/2013	R	2083	261	Equipment	$38	$9,918
15/06/2013	O	2080	342	Shoes	$25	$8,550
4/06/2013	D	1701	221	Equipment	$38	$8,398
14/06/2013	N	2079	77	Accessoires	$5	$385
19/06/2013	S	2084	404	Equipment	$38	$15,352

Re: Mapping sales based on two variables

Sokkorn — Thu, 27 Jun 2013 09:18:34 GMT

Hi,

Tell me if you cannot open file in the attachment.

Regards,

Sokkorn

Re: Mapping sales based on two variables

Thu, 27 Jun 2013 10:44:02 GMT

Thanks for guiding me in the right direction Sokkorn. However, I need to find a workaround since:

(first statement, load inline) contains about 10.000 rows of data
(second statement, ApplyMap) is a dynamic set of sales data

Any suggestions to cover that?

Thanks,

René

Re: Mapping sales based on two variables

Sokkorn — Thu, 27 Jun 2013 10:51:17 GMT

Hi René,

Can you explain me more? I'm completely not get your requirment. There are a lot of method to do ETL in QlikView.

What kind of suggestion you need from us?

Regards,

Sokkorn

Re: Mapping sales based on two variables

Thu, 27 Jun 2013 11:25:49 GMT

Hi Sokkorn,

I've attached the .QVD in which you see the sales data which is available.

In the example you see that in postcode 1685 a sale is made in three categories:

Accessoires
Clothing
Equipment
Shoes

The Excel file shows that each product is linked to a different sales person

Accessoires, Rene
Clothing, Paul
Equipment, Sarah
Shoes, Clyde

The idea is that in the 'sales rep' listbox, you can select your sales person and see how he/she performed.

The structure for the tab 'Mapping' in Excel is done because changes apply on a regular basis. This would be the easiest way to maintain the spreadsheet.

Hope this clarifies my intentions.

Regards,

René