Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sivaprakash_t
Not applicable

Date flags calculation error

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.

Capture.JPG

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Date flags calculation error

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;

3 Replies
sunny_talwar
Not applicable

Re: Date flags calculation error

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;

sivaprakash_t
Not applicable

Re: Date flags calculation error

Hi Sunny,

Thanks for reply, Your code is Working fine.

Gysbert_Wassenaar
Not applicable

Re: Date flags calculation error

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


talk is cheap, supply exceeds demand