logical condition

[buzzy996]

hi ,

some one can help,to get the attached "expected output column" in excel?

[sunny_talwar]

Not really sure how you are getting the number 5 there. Can you explain the calculation again?

[buzzy996]

hi sunny,

sorry i missed one record on my previous attachment file,pfa update one.

thank you

[sunny_talwar]

Is not the best of the solution, but with your current data which only goes back to the max of two rows this is working.

Table1:

LOAD *,

  If(Len(Trim(CalcDate)) > 0, Interval(LASTCHANGEDTS - CalcDate, 'd')) as OutPut;

LOAD TASK_REF,

    TASK_VERSION_KEY,

    VERSIONID,

    STATUS,

    CREATETS,

    LASTCHANGEDTS,

    If(STATUS = 'Reply Received',

    If(Previous(Previous(STATUS)) = 'Awaiting Reply' and TASK_REF = Previous(Previous(TASK_REF)), Previous(Previous(LASTCHANGEDTS)),

    If(Previous(STATUS) = 'Awaiting Reply' and TASK_REF = Previous(TASK_REF), Previous(LASTCHANGEDTS)))) as CalcDate

FROM

[In and output.xls]

(biff, embedded labels, table is Sheet1$);

Depending on how much back you expect your data to go you can expand the following If statement:

If(STATUS = 'Reply Received',

    If(Previous(Previous(STATUS)) = 'Awaiting Reply' and TASK_REF = Previous(Previous(TASK_REF)), Previous(Previous(LASTCHANGEDTS)),

    If(Previous(STATUS) = 'Awaiting Reply' and TASK_REF = Previous(TASK_REF), Previous(LASTCHANGEDTS)))) as CalcDate

Output:

[buzzy996]

Hi Sunny,

yes,your assumption is right..data will change dynamically.

instituted of writing if with previous(previosu()),there is any other solution?

thanks for ur help.

[sunny_talwar]

There must be, just can't think of anything right now. May be swuehl‌ or MarcoWedel‌ can help here

[swuehl]

I think you can do it something along these lines:

INPUT:

LOAD TASK_REF,

    TASK_VERSION_KEY,

    VERSIONID,

    STATUS,

    CREATETS,

    LASTCHANGEDTS

FROM

[In and output (1).xls]

(biff, embedded labels, table is Sheet1$);

LOAD *,

          if(STATUS = 'Reply Received', LASTCHANGEDTS- AwaitDTS) as Output;

LOAD TASK_REF,

    TASK_VERSION_KEY,

    VERSIONID,

    STATUS,

    CREATETS,

    LASTCHANGEDTS,

    if(STATUS = 'Awaiting Reply' and not (peek('STATUS') ='Awaiting Reply' and TASK_REF = peek('TASK_REF')), LASTCHANGEDTS, PEEK('AwaitDTS')) AS AwaitDTS

Resident INPUT

ORDER BY TASK_REF, LASTCHANGEDTS;

DROP TABLE INPUT;

[buzzy996]

hi swuehl

i'll check and update to u,thank you so much.

thnanks

[Kushal_Chawda]

Data:

LOAD TASK_REF,

    TASK_VERSION_KEY,

    VERSIONID,

    STATUS,

    CREATETS,

    LASTCHANGEDTS

FROM

[In and output (1).xls]

(biff, embedded labels, table is Sheet1$);

New:

LOAD *,

if(STATUS='Reply Received',LASTCHANGEDTS-ChangeDate,Null()) as OutPut;

LOAD *,

if(TASK_REF<>Previous(TASK_REF) and STATUS='Awaiting Reply', LASTCHANGEDTS,

if(TASK_REF<>Previous(TASK_REF) and STATUS='Reply Received',Null(),

if(TASK_REF=Previous(TASK_REF) and (previous(STATUS)='Reply Received' and STATUS= 'Awaiting Reply'),LASTCHANGEDTS,

if(TASK_REF=Previous(TASK_REF) and previous(STATUS)='Awaiting Reply',Peek('ChangeDate'),null())))) as ChangeDate

Resident Data

Order by TASK_REF,VERSIONID asc;

DROP Table Data;