Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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.
May be this
Table:
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;
View solution in original post
not sure i understand your logic completely but did you try something like
if(Previous(Transaction)='MAJCTIMMO',1,0) as Immo
Thanks a lot !
