Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
worked like a charm thx