IF Condition

[#1]

Hi,

please look at my example. I have this..

Tabelle:

LOAD * INLINE [

    ID, Date, IO

    1, 1.1.2015, O

    1, 2.1.2015, O

    1, 3.3.2015, I

    1, 4.3.2015, O

    1, 5.3.2015, I

    2, 10.02.2016, O

     2, 10.02.2016, I

];

And I want to have this solution...

ID, Date, Date2

    1, 1.1.2015, 3.3.2015

    1, 2.1.2015, 3.3.2015

    1, 4.3.2015, 5.3.2015

    1, 10.02.2016, 10.02.2016

When Date with IO = I is smaller than Date with IO = O then this should by Date2 but only for the first I min date values...

[#2]

Here this is my solution but it works not fine...

[#3]

okay here is my next solution

two rows are missing

2, 2.1.2015, I

    2, 1.1.2015, O

[swuehl · Accepted solution]

Maybe like this?

Tabelle:

LOAD * INLINE [

    ID, Date, IO

    1, 1.1.2015, O

    1, 2.1.2015, O

    1, 3.3.2015, I

    2, 2.1.2015, I

    2, 1.1.2015, O

    1, 4.3.2015, O

    1, 5.3.2015, I

    2, 10.11.2016, O

    2, 13.11.2016, I

    2, 15.11.2016, I

    2, 14.11.2016, O

];

TMP:

LOAD *, if(IO='I', Date, If(Peek('ID')=ID,Peek('TMP'))) as TMP

Resident Tabelle

ORDER BY ID, Date desc;

RESULT:

LOAD ID, Date, TMP as IDate

RESIDENT TMP

WHERE IO = 'O';

DROP TABLES TMP, Tabelle;