topic Re: condition in Pivot Table between 2 dimensions in QlikView

condition in Pivot Table between 2 dimensions

louisernould — Wed, 25 Nov 2020 16:16:04 GMT

Hi the Qlik Community,

I've a problem about something, and I hope you can help me to solve it ?

I have an excel file, and in the file, there are somes dimensions : the Date, a reference ,an evolution of stock, and some modifications about this stock (Sell and rectification).

I'm looking for a formula which can tell me if a modification on the stock put the stock under Zero during 1 week.

for example, I have this king of table:

Date	Reference	Stock	Rectification	sells
01/08	refA	20	0	0
02/08	refA	20	-10	0
03/08	refA	10	0	5
04/08	refA	5	0	7
05/08	refA	-2	0	0
01/08	refB	20	0	0
02/08	refB	20	-10	0
03/08	refB	10	0	20
04/08	refB	-10	0	5
05/08	refB	-15	0	0

I'm looking for something like that:

Date	reference	Binairy
05/08	refA	1
04/08	refB	1
05/08	refB	1

I already search with getFieldSelections, and some set analysis but it's not helpfull...

if anyone has a great idea, it will be so nice !!

thank you in advance

Louis

Re: condition in Pivot Table between 2 dimensions

MindaugasBacius — Fri, 09 Sep 2016 16:01:43 GMT

I have two solutions:

1. With flag that is created in the load sentence;

2. Without the flag with data we have.

Please take a look at the attachment.

Re: condition in Pivot Table between 2 dimensions

MarcoWedel — Fri, 09 Sep 2016 19:18:02 GMT

maybe

LOAD *,
          -(Stock<0) as Binary
From Yourtable;

hope this helps

regards

Marco

Re: condition in Pivot Table between 2 dimensions

louisernould — Mon, 12 Sep 2016 12:29:20 GMT

Hi Marco and Mindaugas,

First, thank for your quick answers, you're working faster than me

Both of your solutions work to see how many reference has a stock <0. But I'm looking for the condition which can tell me if I have a stock <0 And a rectification > 0 (at least one) a week before.

for this example, it will return nothing : I have a Stock <0 on 05/08, but no rectification.

Date	Reference	Stock	sells
01/08	refA	10	0
02/08	refA	10	0
03/08	refA	10	5
04/08	refA	5	7
05/08	refA	-2	0

Otherwise, for this example,

Date	Reference	Stock	Rectification	sells
01/08	refA	10	0	0
02/08	refA	10	10	0
03/08	refA	0	0	5
04/08	refA	-5	0	7
05/08	refA	-12	0	0

It will return

Date	reference	Binairy
04/08	refA	1
05/08	refA	1

Hope your can still help me

regards,

Louis

Re: condition in Pivot Table between 2 dimensions

Or — Mon, 12 Sep 2016 12:53:13 GMT

Perhaps something along the lines of:

If(Stock < 0 AND aggr(sum(Rectification,Reference))>0,1) ?

This isn't specific to one week - it will check your entire selected date population. If you need it to specifically check one week back, you'll need to look into a range function / inter-record function instead.