if Statment with previous or any other suggestion

[#1]

Hi guys

i have some sampledata but i cant get my If statment to work properly. It's a bit complicated but maybe somebody can help me out

This is the sample below

I want to know if a customer bought a Product in the 100 series and at the same date or six months before bought he/she bought a Product in the 200 series . Can I solve this with a IF statement and a PREVIOUS statement? or is there any other sulotion that's better to solve this problem?

thx Theo

Customer	ID	Status	Orgname	Date	Product
48226	4512777	Done	Arpa	2011-02-28	103
48226	7968279	Done	Arpa	2012-11-28	101
48226	11758575	Done	Arpa	2014-11-17	101
48226	13524439	Done	Arpa	2015-10-15	111
34097	14338835	Done	Bionic	2016-03-08	112
34097	17512118	Done	Bionic	2017-09-14	112
53929	8509813	Done	Natsa	2013-03-14	103
34301	9501047	Done	Gerud	2013-09-26	101
34301	9501048	Done	Gerud	2013-09-26	205
34301	9501050	Done	Gerud	2013-09-26	103
34301	6168311	Done	Berud	2012-01-03	103
33929	701178	Done	Berud	2009-02-04	103
33929	1030821	Done	Berud	2009-04-16	103
60000	14697650	Done	Bluqa	2016-05-11	103
60000	14750969	Done	Bluqa	2016-05-19	204
60000	14795514	Done	Bluqa	2016-05-26	206
60000	14918893	Done	Bluqa	2016-06-17	103

[marcus_sommer · Accepted solution]

I'm not sure if you could get it to work with previous/peek-functions within if-loops because you would need to define how many iterations are max. possible and to sort your table to all of your conditions. I would probably try to get this informations with join/keep-loadings like:

table1:

load Customer, Date from Source where Product = '1??';

     concatenate(table1)

load Customer, addmonths(Date, -6) as Date from Source where Product = '1??';

inner join(table1)

load Customer, Date from Source where Product = '2??';

- Marcus

[#3]

worked like a charm thx