Date flags calculation error

[Anonymous]

Hi Friends,

i have some fields like Branch, Case,Down_since and Up_Since, now my issue is where ever Up_Since is null, i need to captured that down since time and more than that down since time in Branch ,calculated as flag,I'm attaching one excel sample data in that manually i created on column OutPut.i'm expecting output like that. Please help me out.

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD IP_Address,

    BranchCode,

    Case_ID,

    Down_Since,

    Up_Since

FROM

CH146_20160405_144011.xls

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

FinalTable:

LOAD *,

  If(IP_Address = Peek('IP_Address') and BranchCode = Peek('BranchCode'),

  If(Up_Since = '-', 1, Peek('Flag')), 0) as Flag

Resident Table

Order By IP_Address, BranchCode, Down_Since;

DROP Table Table;

or this if its actually Null

Table:

LOAD IP_Address,

    BranchCode,

    Case_ID,

    Down_Since,

    Up_Since

FROM

CH146_20160405_144011.xls

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

FinalTable:

LOAD *,

If(IP_Address = Peek('IP_Address') and BranchCode = Peek('BranchCode'),

  If(Len(Trim(Up_Since)) = 0, 1, Peek('Flag')), 0) as Flag

Resident Table

Order By IP_Address, BranchCode, Down_Since;

DROP Table Table;

[Anonymous]

Hi Sunny,

Thanks for reply, Your code is Working fine.

[Gysbert_Wassenaar]

If Sunny's answer solved your problem then please mark it as the correct answer.