Picking data from non selected fields - Qlik Community

Report Inappropriate Content · ‎2010-05-10

Hi Guys,

I was wondering if it's possible to obtain "non-selected data" 'automaticly'?

Let me explain:

If this is my Source Dataset:

Received	Article	DocumentNumber	Qty Received	Amount Received
05/12/2008	Art3	2000	8	5200
20/01/2009	Art3	3000	11	230
21/01/2009	Art3	3001	12	120
05/12/2009	Art4	4000	60	300
05/12/2009	Art1	4001	26	131
05/12/2009	Art1	4002	70	349
20/12/2009	Art1	4003	56	271
08/01/2010	Art5	4011	5	3500
08/01/2010	Art1	6000	96	444
15/01/2010	Art1	3200	88	52
15/01/2010	Art1	4005	18	103
15/01/2010	Art1	4006	40	176
15/01/2010	Art1	4007	28	182
15/01/2010	Art1	5000	6	4000
21/01/2010	Art3	4599	12	120
04/02/2010	Art4	4001	11	7500
05/02/2010	Art2	4004	20	200
05/02/2010	Art2	4598	2	1800
02/03/2010	Art5	8000	1	100

And I want to select by Month & Year to get my latest stock movements (stock overview) for ALL my articles, I can't find the correct syntax herefore.

If I select 02/2010 I get:

04/02/2010

Art4

4001

11

7500

05/02/2010

Art2

4598

2

1800

This is correct because these are the latest dates with the highest documentnumbers, so in fact my latest receipts.

But I want to have a view over ALL my articles and their movements -->

selection	feb/10
		Art1	15/01/2010	5000	6	4000
		Art2	05/02/2010	4598	2	1800
		Art3	21/01/2010	4599	12	120
		Art4	04/02/2010	4001	11	7500
		Art5	08/01/2010	4011	5	3500

because in feb 2010 I had 2 movements for Artcs 4 & 2, but in Jan 2010 my last movements for Artcs 1,3,5 were:

Art1

15/01/2010

5000

6

4000

Art3

21/01/2010

4599

12

120

Art5

08/01/2010

4011

5

3500

Who can help me out? Is it possible in QV? Because how I understood to engine of QV it shows just all the 'related' values to your selection.

(as in feb 2010, artcs 2 and 4)

Many thanks

John

johnw · ‎2010-05-10

I don't know if I completely understood, but here's one approach that uses an AsOfMonth and QlikView's normal associative logic. By selecting an AsOfMonth instead of ReceivedMonth, you include in the data set all receipts up to that date. Then you "just" need to exclude everything but the most recent receipt on or before that date. It seems to work. It at least produces the chart you requested.

Report Inappropriate Content · ‎2010-05-11

Hi John,

thanks for your suggestion. It looks like it works as you described.

I couldn't find it on my own, but again something new learned! Many Thanks!!

Can I ask you a second question about this datamodel? How can I now connect my (2nd table) overview of stock? Which key to use? How to link with these results?

I have a second table, showing by month/year by Article the qty in Stock.

I tries with the key

MONTH

(

(Month) & ' ' & Year AS AsOfMonth,

but this doesn't show the correct results.

PS: How can I send you/publish my .QVW and .XLSX?

By tab Options, I can only post 1 attach??

In the excel, you'll find a 1*tab for Receivings, a 2*tab for Stock overview and a 3*tab for the wanted results..

Table receivings:

ReceivedDate	Article	DocumentNumber	QtyReceived	AmountReceived
05/12/2008	Article3	2000	8	5200
20/01/2009	Article3	3000	11	230
21/01/2009	Article3	3001	12	120
05/12/2009	Article4	4000	60	300
05/12/2009	Article1	4001	26	131
05/12/2009	Article1	4002	70	349
20/12/2009	Article1	4003	56	271
08/01/2010	Article5	4011	5	3500
08/01/2010	Article1	6000	96	444
15/01/2010	Article1	3200	88	52
15/01/2010	Article1	4005	18	103
15/01/2010	Article1	4006	40	176
15/01/2010	Article1	4007	28	182
15/01/2010	Article1	5000	6	4000
21/01/2010	Article3	4599	12	120
04/02/2010	Article4	4001	11	7500
05/02/2010	Article2	4004	20	200
05/02/2010	Article2	4598	2	1800
02/03/2010	Article5	8000	1	100

Table Stock Overview:

Article	Month	Year	Stock	Price
Article1	12	2008	0	10
Article1	1	2009	0	10
Article1	2	2009	0	10
Article1	3	2009	0	10
Article1	4	2009	0	10
Article1	5	2009	0	10
Article1	6	2009	0	10
Article1	7	2009	0	10
Article1	8	2009	0	10
Article1	9	2009	0	10
Article1	10	2009	0	10
Article1	11	2009	0	10
Article1	12	2009	152	10
Article1	1	2010	428	10
Article1	2	2010	428	10
Article1	3	2010	428	10
Article2	12	2008	0	100
Article2	1	2009	0	100
Article2	2	2009	0	100
Article2	3	2009	0	100
Article2	4	2009	0	100
Article2	5	2009	0	100
Article2	6	2009	0	100
Article2	7	2009	0	100
Article2	8	2009	0	100
Article2	9	2009	0	100
Article2	10	2009	0	100
Article2	11	2009	0	100
Article2	12	2009	0	100
Article2	1	2010	0	100
Article2	2	2010	22	100
Article2	3	2010	22	100
Article3	12	2008	8	5
Article3	1	2009	31	5
Article3	2	2009	31	5
Article3	3	2009	31	5
Article3	4	2009	31	5
Article3	5	2009	31	5
Article3	6	2009	31	5
Article3	7	2009	31	5
Article3	8	2009	31	5
Article3	9	2009	31	5
Article3	10	2009	31	5
Article3	11	2009	31	5
Article3	12	2009	31	5
Article3	1	2010	43	5
Article3	2	2010	43	5
Article3	3	2010	43	5
Article4	12	2008	0	40
Article4	1	2009	0	40
Article4	2	2009	0	40
Article4	3	2009	0	40
Article4	4	2009	0	40
Article4	5	2009	0	40
Article4	6	2009	0	40
Article4	7	2009	0	40
Article4	8	2009	0	40
Article4	9	2009	0	40
Article4	10	2009	0	40
Article4	11	2009	0	40
Article4	12	2009	60	40
Article4	1	2010	60	40
Article4	2	2010	71	40
Article4	3	2010	71	40
Article5	12	2008	0	70
Article5	1	2009	0	70
Article5	2	2009	0	70
Article5	3	2009	0	70
Article5	4	2009	0	70
Article5	5	2009	0	70
Article5	6	2009	0	70
Article5	7	2009	0	70
Article5	8	2009	0	70
Article5	9	2009	0	70
Article5	10	2009	0	70
Article5	11	2009	0	70
Article5	12	2009	0	70
Article5	1	2010	5	70
Article5	2	2010	5	70
Article5	3	2010	6	70

Wanted Result:

		RECEIVINGS					STOCK OVERVIEW
		Article	ReceivedDate	DocumentNumber	QtyReceived	AmountReceived	Year	Month	Article	Stock	Price
-->	dec/09	Article1	20/12/2009	4003	56	271	2009	12	Article1	152	10
		Article2	x	x	x	x	2009	12	Article2	0	100
		Article3	21/01/2009	3001	12	120	2009	12	Article3	31	5
		Article4	05/12/2009	4000	60	300	2009	12	Article4	60	40
		Article5	x	x	x	x	2009	12	Article5	0	70
-->	jan/10	Article1	15/01/2010	5000	6	4000	2010	1	Article1	428	10
		Article2	x	x	x	x	2010	1	Article2	0	100
		Article3	21/01/2010	4599	12	120	2010	1	Article3	43	5
		Article4	05/12/2009	4000	60	300	2010	1	Article4	60	40
		Article5	08/01/2010	4011	5	3500	2010	1	Article5	5	70
-->	feb/10	Article1	15/01/2010	5000	6	4000	2010	2	Article1	428	10
		Article2	05/02/2010	4598	2	1800	2010	2	Article2	22	100
		Article3	21/01/2010	4599	12	120	2010	2	Article3	43	5
		Article4	04/02/2010	4001	11	7500	2010	2	Article4	71	40
		Article5	08/01/2010	4011	5	3500	2010	2	Article5	5	70
-->	mrt/10	Article1	15/01/2010	5000	6	4000	2010	3	Article1	428	10
		Article2	05/02/2010	4598	2	1800	2010	3	Article2	22	100
		Article3	21/01/2010	4599	12	120	2010	3	Article3	43	5
		Article4	04/02/2010	4001	11	7500	2010	3	Article4	71	40
		Article5	02/03/2010	8000	1	100	2010	3	Article5	6	70

(