topic Re: Creating flag for Dates in QlikView

Creating flag for Dates

Wed, 15 Jun 2016 05:34:57 GMT

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.

Re: Creating flag for Dates

Anonymous — Wed, 15 Jun 2016 07:18:46 GMT

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!!!

Re: Creating flag for Dates

Wed, 15 Jun 2016 07:57:20 GMT

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;

Re: Creating flag for Dates

Anonymous — Wed, 15 Jun 2016 08:05:48 GMT

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!

Re: Creating flag for Dates

maniram23 — Wed, 15 Jun 2016 08:18:41 GMT

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$);