Creating flag for Dates

[#1]

Hi friends,

I have table with Company, Invoice, Created on and Closed on fields in a table, i need to calculate flag

Scenario: in my first Invoice have closed on is 4/28/2016  6:09:00 PM based on this i need to calculate closed on less than Created on invoices. please Help me out of this issue. thanks in Advance.

[Anonymous]

Hi Siva,

Try this plz:

Invoice_Aux:

LOAD *

FROM

[new doc.xls]

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

Invoice:

LOAD Company,

    Invoice,

    [Created on],

    [Closed On],

    If(IsNull(Previous(Invoice)),[Closed On],

        If(Previous(Company) <> Company,[Closed On],

            IF([Closed On]>Peek(PrevDate,-1),[Closed On],Peek(PrevDate,-1))))            as PrevDate,

    If(IsNull(Previous(Invoice)),0,

        If(Previous(Company) <> Company,0,

            IF([Closed On]>Peek(PrevDate,-1),0,1)))            as FlagDate

RESIDENT Invoice_Aux

ORDER BY Company ASC, Invoice ASC;

DROP TABLE Invoice_Aux;

Regards!!!

[#3]

Test:

Load * Inline [

Company, Invoice, Created on, Closed on

Sony, PO000001468871, 04/08/2016 2:35:00 PM, 04/28/2016 06:09:00 PM

Sony, PO000001468872, 04/09/2016 7:05:21 PM, 04/11/2016 09:54:13 AM

Sony, PO000001468873, 04/11/2016 12:37:33 PM, 04/11/2016 01:08:28 PM

Sony, PO000001468887, 04/28/2016 12:27:05 PM, 04/28/2016 05:59:01 PM

Sony, PO000001468888, 04/28/2016 8:57:30 PM, 04/29/2016 09:35:40 AM

Sony, PO000001468889, 04/30/2016 7:11:13 PM, 05/01/2016 12:00:00 AM

];

NoConcatenate

Test1:

Load *

Resident Test

order by Invoice asc;

Drop table Test;

Let vInvoice = peek('Closed on', 0, Test1);

NoConcatenate

Test2:

load Company, Invoice, [Created on], [Closed on],

if(Date <= [Closed on], 0, 1) as Flag;

Load *,

'$(vInvoice)' as Date

Resident Test1;

Drop table Test1;

[Anonymous]

Hi Saikrishna,

What happen if you have 2 or more companies? And, what happen if after PO000001468889 you have another line with greather date than fistr line but less than PO000001468889?

Regards!

[maniram23]

Hi,

TABLE1:

Load  *  ,

if( [Closed on]<4/28/2016  6:09:00 PM, 0, 1) as Flag

FROM

[new doc.xls]

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