topic AW:Re: Help needed to link two tables in QlikView

Help needed to link two tables

Anonymous — Tue, 06 Jul 2010 07:32:24 GMT

Hi QV users.

I have a problem with two tables. The first table contains employees in different sections per month. Only if an employee is in the specific section it is marked with a "1".

The other table contains sales per employee per month.

My problem is that employee no. 3 is not in the company in october, november and december and employee is not in the company in january and february. But for some reason some of their sales are registered in these month.

To link the two tables I have created a key with employee and month. When I create a Pivot table with numbers of employees per section per month it shows everything correct, but when I create a Pivot table with sales per employee per month it shows the sales for employee no. 3 and 4 in the months they are not in the company in a separate row and column marked "-".

Can you help me to show the sales in the correct months but in a new row below the sections with the text "Sales not correct registered"? It should also be possible to select the value "Sales not correct registered" in the Section listbox.

Any ideas?

Thanks in advance

Johnni

Help needed to link two tables

Anonymous — Tue, 06 Jul 2010 07:35:59 GMT

Hi QV users.

Please find a QV-file and a Excel-file attached in the two first post.

Help needed to link two tables

Tue, 06 Jul 2010 07:47:33 GMT

On the Sales tab of the xlsx Emp 3 has sales entered for Oct, Nov and Dec, and Emp4 has sales for Jan and Feb.

The join you have is why the sales are showing.

AW:Re: Help needed to link two tables

brenner_martina — Tue, 06 Jul 2010 08:05:11 GMT

Hi, Johnni,

this is very easy, the sum 29 are employee No 4 in Jan and Feb and No 3 in Oct, Nov and Dec. You entered them a sales value but they didn't work in this months. In your pivot you can supress showing NULL-values for all three dimensions dept, employee and Month. Look at the uploaded File.

AW:Re: Help needed to link two tables

Anonymous — Tue, 06 Jul 2010 09:13:14 GMT

Hi Martina,

that was a good suggestion but unfortunately not the answer I was looking for. The sales must be shown in the month they are registered but with a new row created called "Sales not correct registered". If I suppress when value is Null the sales of 29 units is missed.

Below I have manually made a solution which I want QlikView to show unless there is a better way to do it.

Best regards

Johnni

Section	Employee	01-01-2010	01-02-2010	01-03-2010	01-04-2010	01-05-2010	01-06-2010	01-07-2010	01-08-2010	01-09-2010	01-10-2010	01-11-2010	01-12-2010	Total
A	1	5	6	4	6	3	4	8	4	6	9	5	5	65
A	2	1	4	1	2									8
A	4								5	6	4			15
B	2					3	6	7	2	5	6	4	7	40
B	4			4			4	8						16
C	4				6	3						8	2	19
D	3	3	6	9	8	4	1	3	5	7				46
Sales not correct registered		9	5								4	6	5	29
Total		18	21	18	22	13	15	26	16	24	23	23	19	238

Help needed to link two tables

Anonymous — Wed, 07 Jul 2010 19:08:47 GMT

Hi QV users.

Maybe it's because of the summer holiday that I don't get any replies solving my problem but I still hope some of you out there can help me.