topic Re: Join? Link? Map? Help in QlikView

Join? Link? Map? Help

Anonymous — Wed, 16 Sep 2015 14:44:17 GMT

I have a table of sales by supplier:

Month	Supplier	Sales
Jan	A	1488
Jan	B	1301
Jan	C	1267
Feb	A	1373
Feb	B	1307
Feb	C	1228
Mar	A	1816
Mar	B	1101
Mar	C	1885

And a table of sales by customer:

Month	Customer	Sales
Jan	X	2668
Jan	Y	978
Jan	Z	410
Feb	X	815
Feb	Y	348
Feb	Z	2745
Mar	X	1515
Mar	Y	1734
Mar	Z	1553

I want to create a table that shows sales by customer AND supplier that would look like this:

Month	Supplier	Customer	Sales
Jan	A	X	979
Jan	A	Y	359
Jan	A	Z	150
Jan	B	X	856
Jan	B	Y	314
Jan	B	Z	132
Jan	C	X	833
Jan	C	Y	306
Jan	C	Z	128
Feb	A	X	286
Feb	A	Y	122
Feb	A	Z	964
Feb	B	X	273
Feb	B	Y	116
Feb	B	Z	918
Feb	C	X	256
Feb	C	Y	109
Feb	C	Z	863
Mar	A	X	573
Mar	A	Y	656
Mar	A	Z	587
Mar	B	X	347
Mar	B	Y	398
Mar	B	Z	356
Mar	C	X	595
Mar	C	Y	681
Mar	C	Z	610

I have no other information to link the 2 tables other than date as it's just forecast information. How is this possible?

Thanks

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 14:49:03 GMT

I have a question - both tables have sales but one is for customer and the other is for supplier - If you wnated to put everytung in one table, wouldnt you need sales for both?

if you wnat to create one table from 2 and you want columns from both, you would need to do a join

Re: Join? Link? Map? Help

Anonymous — Wed, 16 Sep 2015 14:52:49 GMT

Its just forecast information so I have sales by customer or sales by supplier but I don't have both. That's what I want to create in the script. A join doesn't work as I get null values for supplier where there is a customer and vice versa

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 15:16:01 GMT

I still do not understand which of the 2 Sales fields you have, you are showing in your consolidated table

attached is the output based on the sample data you provided (I created 2 excel spreadsheets and renamed the sales fileds to avoid synthetic key

are you looking for a relationship between customer and supplier?

Re: Join? Link? Map? Help

Anonymous — Wed, 16 Sep 2015 15:19:28 GMT

can you attach the qvw so I can have a look at what you did?

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 15:20:29 GMT

sure - see attached

Re: Join? Link? Map? Help

Anonymous — Wed, 16 Sep 2015 15:23:09 GMT

based on my data in January supplier A supplied 36.7% of all sales in January therefore supplier A sold 36.7% of sales to customer X. 36.7% of 2668 is 979. Does that make sense?

Re: Join? Link? Map? Help

Digvijay_Singh — Wed, 16 Sep 2015 15:26:01 GMT

I think without knowing which supplier supplies to which customer and the value of sales between them, it wouldn't be possible to create the right model.

Re: Join? Link? Map? Help

Anonymous — Wed, 16 Sep 2015 15:27:46 GMT

I just want to assume that proportion of sales by supplier applies to each individual customer. I know that in January supplier A supplied 36.7% of all sales in January therefore supplier A sold 36.7% of sales to customer X.

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 15:29:39 GMT

I agree based on the numbers in January(total customer sales of 4056) and supplier sales for Supplier A (1488) equates to supplier A accounting for 36.7% of the January. I am not sure that the percentage is evenly split amongst the customers, unless that is a assumption or understanding you have

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 15:30:39 GMT

based on your last reply, it sound like that is an assumption you want to make

Re: Join? Link? Map? Help

Anonymous — Wed, 16 Sep 2015 15:32:15 GMT

yes, that's correct

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 15:36:23 GMT

see attached

Re: Join? Link? Map? Help

Wed, 16 Sep 2015 15:46:35 GMT

see attached