Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Can you give desire result from your table above?
Regards,
Sokkorn
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 |
May be CROSSTABLE? try using this in the script.
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 |
Hi,
Tell me if you cannot open file in the attachment.
Regards,
Sokkorn
Thanks for guiding me in the right direction Sokkorn. However, I need to find a workaround since:
Any suggestions to cover that?
Thanks,
René
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
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:
The Excel file shows that each product is linked to a different sales person
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é