topic Re: How to test in script each fields depending on another field ? in QlikView

How to test in script each fields depending on another field ?

Anonymous — Mon, 08 Oct 2018 09:10:21 GMT

Hi community,

I'm having trouble with the following problem.

I would like to add a field that test for each "Article" if the last "Transaction" is "MAJCTIMMO" (sorted by Date).

If it's true, it changes the value of a flag to his opposite (0 to 1 or 1 to 0).

If we take the data, my table looks like this :

LOAD * INLINE [

Article, Transaction, Date

02, CREPIECE, 24/07/2015

02, MAJQTEST, 27/07/2015

02, MAJCTIMMO, 17/11/2015

02, ENRQUANSTOCK, 14/10/2016

02, MAJQTEIN, 14/10/2016

02, ENRQUANSTOCK, 29/11/2017

02, MAJQTEIN, 29/11/2017

02, MAJCTIMMO, 30/01/2018

11, CREPIECE, 28/08/2016

11, MAJQTEST, 30/08/2016

11, MAJCTIMMO, 12/04/2018

22, CREPIECE, 14/01/2014

22, MAJQTEST, 17/01/2014

22, MAJCTIMMO, 06/08/2015

22, ENRQUANSTOCK, 30/03/2017

];

And the result would be this :

LOAD * INLINE [

Article, Transaction, Date, Immo

02, CREPIECE, 24/07/2015, 0

02, MAJQTEST, 27/07/2015, 0

02, MAJCTIMMO, 17/11/2015, 1

02, ENRQUANSTOCK, 14/10/2016, 1

02, MAJQTEIN, 14/10/2016, 1

02, ENRQUANSTOCK, 29/11/2017, 1

02, MAJQTEIN, 29/11/2017, 1

02, MAJCTIMMO, 30/01/2018, 0

02, MAJCTIMMO, 30/01/2018, 1

11, CREPIECE, 28/08/2016, 0

11, MAJQTEST, 30/08/2016, 0

11, MAJCTIMMO, 12/04/2018, 1

22, CREPIECE, 14/01/2014, 0

22, MAJQTEST, 17/01/2014, 0

22, MAJCTIMMO, 06/08/2015, 1

22, ENRQUANSTOCK, 30/03/2017, 1

];

I don't know how can I make this. With a for each ? Something else ? I'm a little bit confused.

I hope someone can help me.

Thanks in advance,

Nicolas.

Re: How to test in script each fields depending on another field ?

dplr-rn — Mon, 08 Oct 2018 18:36:58 GMT

not sure i understand your logic completely but did you try something like

if(Previous(Transaction)='MAJCTIMMO',1,0) as Immo

Re: How to test in script each fields depending on another field ?

sunny_talwar — Mon, 08 Oct 2018 19:31:46 GMT

May be this

Table:

LOAD * INLINE [

Article, Transaction, Date

02, CREPIECE, 24/07/2015

02, MAJQTEST, 27/07/2015

02, MAJCTIMMO, 17/11/2015

02, ENRQUANSTOCK, 14/10/2016

02, MAJQTEIN, 14/10/2016

02, ENRQUANSTOCK, 29/11/2017

02, MAJQTEIN, 29/11/2017

02, MAJCTIMMO, 30/01/2018

11, CREPIECE, 28/08/2016

11, MAJQTEST, 30/08/2016

11, MAJCTIMMO, 12/04/2018

22, CREPIECE, 14/01/2014

22, MAJQTEST, 17/01/2014

22, MAJCTIMMO, 06/08/2015

22, ENRQUANSTOCK, 30/03/2017

];

FinalTable:

LOAD *,

If(Article = Previous(Article), If(Transaction = 'MAJCTIMMO', If(Peek('Immo') = 1, 0, 1), Alt(Peek('Immo'), 0)), 0) as Immo

Resident Table

Order By Article, Date;

DROP Table Table;

Re: How to test in script each fields depending on another field ?

Anonymous — Tue, 09 Oct 2018 12:56:46 GMT

Thanks a lot !