topic Re: extracting date in QlikView

extracting date

Wed, 12 Jun 2013 11:40:36 GMT

Hi All,

I have a query mentioned below:

LOAD

[Case Created] as AsOfDttm,

Date#(Subfield([Case Created],'/',2)) as AsOfDttm_Day,

Date#([Case Created]) - 7 as Date_LastSeven,

'7daysback' as PreviousCurrentFlag;

LOAD DISTINCT [Case Created]

RESIDENT Report;

This should return 4 columns,

1. AsOfDttm

2. AsOfDttm_Day

3. Date_LastSeven - it should return 7 days before's date

4. PreviousCurrentFlag

Getting all the values except the 3rd one.

Can anyone please help me on this..

Thanks in advance

Re: extracting date

Wed, 12 Jun 2013 11:49:21 GMT

TRY THIS

Date#([Case Created],'DD-MM-YYYY') - 7 as Date_LastSeven

HERE BOLD PART IS THE FORMAT OF YOUR DATE FIELD THAT IS [Case Created]

YOU CAN SET THE BOLD PART ACCORDING TO YOUR FORMAT OF FIELD

FORMAT OF DATE--

DATE(DATE#(FIELDNAME,'DD/MM-YYYY'),'DD-MM-YYYY')

Re: extracting date

Wed, 12 Jun 2013 11:52:05 GMT

TRY THIS

NUM(Date([Case Created])) - 7 as Date_LastSeven

OR THIS ONE

DATE(NUM(Date([Case Created])) - 7) as Date_LastSeven

Re: extracting date

jagan — Wed, 12 Jun 2013 11:53:24 GMT

Hi,

Try like this

LOAD

[Case Created] as AsOfDttm,

Date#(Subfield([Case Created],'/',2)) as AsOfDttm_Day,

Date(Date#([Case Created], 'MM/DD/YYYY') - 7) as Date_LastSeven,

'7daysback' as PreviousCurrentFlag;

LOAD DISTINCT [Case Created]

RESIDENT Report;

In the above script change the format of the date to your date format.

Hope this helps you.

Regards,

Jagan.

Re: extracting date

Wed, 12 Jun 2013 12:04:39 GMT

non of them worked fokes

Re: extracting date

Wed, 12 Jun 2013 12:04:53 GMT

non of them worked fokes

Re: extracting date

Wed, 12 Jun 2013 12:08:47 GMT

can u show the original data?

Re: extracting date

Wed, 12 Jun 2013 12:56:58 GMT

it is in string

in the format MM/DD/YYYY hh:mm:ss

Re: extracting date

Wed, 12 Jun 2013 12:58:22 GMT

already tried.. dint work

Re: extracting date

Wed, 12 Jun 2013 13:12:08 GMT

TRY THIS

LOAD

[Case Created] as AsOfDttm,

Date#(Subfield([Case Created],'/',2)) as AsOfDttm_Day,

Date(Num(Date(date#([Case Created],'MM/DD/YYYY hh:mm:ss'),'DD-MM-YYYY')) - 7) as Date_LastSeven,

'7daysback' as PreviousCurrentFlag;

LOAD DISTINCT [Case Created]

RESIDENT Report;

Re: extracting date

flipside — Wed, 12 Jun 2013 13:16:50 GMT

Does the date portion format of your TimestampFormat match your data's format?

This should work, but breaks if the above mismatches.

Date([Case Created]-7) as Date_LastSeven,

flipside

EDIT: This would get round it ...

Date(Subfield([Case Created],' ',1)-7) as Date_LastSeven,