Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Mapping sales based on two variables

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

Post CodeAccessoiresShoesEquipmentClothing
1680LesPaulLesAllan
1685SarahClydePaulPaul
1700ClydeSarahAllanLes
1701PaulLesReneSarah
1710SarahReneSarahRene

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.

1 Solution

Accepted Solutions
Sokkorn
Honored Contributor

Re: Mapping sales based on two variables

Hi,

Tell me if you cannot open file in the attachment.

Regards,

Sokkorn

8 Replies
Sokkorn
Honored Contributor

Re: Mapping sales based on two variables

Hi,

Can you give desire result from your table above?

Regards,

Sokkorn

Not applicable

Re: Mapping sales based on two variables

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

Sum of Total SalesSales Rep
ProductSarahPaulReneAllanLesClydeGrand 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
MVP
MVP

Re: Mapping sales based on two variables

May be CROSSTABLE? try using this in the script.

Not applicable

Re: Mapping sales based on two variables

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

DateCustomerPost CodeQuantiyProductUnit PriceTotal Sales
10/06/2013J2035187Shoes$25$4,675
22/06/2013V1680493Shoes$25$12,325
5/06/2013E1710181Clothing$13$2,353
21/06/2013U203287Equipment$38$3,306
4/06/2013D1701438Shoes$25$10,950
11/06/2013K207586Shoes$25$2,150
1/06/2013A1680473Shoes$25$11,825
24/06/2013X207671Accessoires$5$355
6/06/2013F2031354Shoes$25$8,850
2/06/2013B1685208Clothing$13$2,704
12/06/2013L2076169Clothing$13$2,197
5/06/2013E1710163Shoes$25$4,075
20/06/2013T2031477Shoes$25$11,925
18/06/2013R2083261Equipment$38$9,918
15/06/2013O2080342Shoes$25$8,550
4/06/2013D1701221Equipment$38$8,398
14/06/2013N207977Accessoires$5$385
19/06/2013S2084404Equipment$38$15,352
Sokkorn
Honored Contributor

Re: Mapping sales based on two variables

Hi,

Tell me if you cannot open file in the attachment.

Regards,

Sokkorn

Not applicable

Re: Mapping sales based on two variables

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é

Sokkorn
Honored Contributor

Re: Mapping sales based on two variables

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

Not applicable

Re: Mapping sales based on two variables

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:

  1. Accessoires
  2. Clothing
  3. Equipment
  4. Shoes

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

  1. Accessoires, Rene
  2. Clothing, Paul
  3. Equipment, Sarah
  4. 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é