topic Re: compare 2 dates in different format in QlikView

compare 2 dates in different format

Tue, 25 Nov 2014 10:08:32 GMT

I want to compare two dates, first in format like 201405 and other in 5/1/2014.

I am using it in straight table and if condition gets true, then data for that date should display.

How to do that?

Thanks

Saurabh

Re: compare 2 dates in different format

PrashantSangle — Tue, 25 Nov 2014 10:17:52 GMT

Hi,

First convert both date in one format then compare it

I prefer convert it into Number then compare

Try like

Num(date(date#(firstDateField,'YYYYMM')))

AND

Num(secondDateField)

Regards

Re: compare 2 dates in different format

Anonymous — Tue, 25 Nov 2014 10:18:15 GMT

First convert them to QlikView dates:

date ( date# ( '201405 ' , 'YYYYMM' ) , 'DD MMM YYYY' )

date ( date# ( '5/1/2014 ' , 'D/MM/YYYY' ) , 'DD MMM YYYY' )

and then compare them.

Re: compare 2 dates in different format

its_anandrjs — Tue, 25 Nov 2014 10:18:33 GMT

Provide any sample file.

Regards

Anand

Re: compare 2 dates in different format

ashfaq_haseeb — Tue, 25 Nov 2014 10:37:54 GMT

Hi,

Can you provide same data to look over?

Regards

ASHFAQ

Re: compare 2 dates in different format

Tue, 25 Nov 2014 10:38:20 GMT

=count({<Num(date(date#([Year Month],'YYYYMM')))=Num(Date)>}Product)

I tried something like this, but it's giving error.

Re: compare 2 dates in different format

anbu1984 — Tue, 25 Nov 2014 10:40:29 GMT

=count({<Date={'$(=date(date#([Year Month],'YYYYMM'),'MM/DD/YYYY'))'}>}Product)

Change 'MM/DD/YYYY' to format of field Date

Re: compare 2 dates in different format

Tue, 25 Nov 2014 10:43:07 GMT

=count({<date ( date# ( '201405 ' , 'YYYYMM' ) , 'DD MMM YYYY' )=date ( date# ( '5/1/2014 ' , 'D/MM/YYYY' ) , 'DD MMM YYYY' )>}product)

it's giving error.

Re: compare 2 dates in different format

PrashantSangle — Tue, 25 Nov 2014 10:48:22 GMT

Hi,

In set Analysis you cannot compare like this,

Try With IF statement

=count(if(Num(date(date#([Year Month],'YYYYMM')))=Num(Date),Product))

Regards

Re: compare 2 dates in different format

jagan — Wed, 26 Nov 2014 02:49:37 GMT

Hi,

Can you attach some sample data?

Try like this in script

TableName:

LOAD

If(Date1Formatted = Date2Formatted, 1, 0) AS Flag;

LOAD

Date(Date#(Date1, 'YYYYMM')) AS Date1Formatted,

Date(Date#(Date2, 'M/D/YYYY')) AS Date2Formatted

FROM DataSource;

Now just use

Count({<Flag={1}>} Product)

Hope this helps you.

Regards,

Jagan.