topic Re: Date in QlikView

Date

Tue, 22 Dec 2015 03:45:57 GMT

Hi Friends,

I have two different excels IR dump and stratification Audit, with no relation

in IR dump there is date field, stratification Audit don't have.

I would like to create a relation b/w the dumps using date ..

so what i have done is renamed the excel with prefix 12092015 and it became as 12092015 stratification Audit and extracted that.

I used both Filename() and filetime() to get the date field,

left(filename(),+9),

i got the output as 12092015.

now what happening is i am not able to format the date as 12/09/2015 and i want to add two days for that

Eg: date(12/09/2015)+2 =12/11/2015, so that i can make relation between two tables.

I used the below one, but it is not working..

Date(Date#(left(filename(),+9),'MM/DD/YYYY'),'DD/MM/YYYY'),

Date(Date#(left(filetime(),+9),'MM/DD/YYYY'),'DD/MM/YYYY')

please help me to do this.

Re: Date

sunny_talwar — Tue, 22 Dec 2015 03:51:02 GMT

May be this (Without slashes) and may be MM/DD/YYYY to convert it into date with slashes in the format MM/DD/YYYY assuming the date is December 9th, 2015.

Date(Date#(left(filename(),+9),'MMDDYYYY'),'MM/DD/YYYY'),

Date(Date#(left(filetime(),+9),'MMDDYYYY'),'MM/DD/YYYY')

UPDATE: Also I am not sure if you need the plus ( + ) sign before the 9, I would just use 9 without the plus sign

Date(Date#(Left(filename(), 9),'MMDDYYYY'),'MM/DD/YYYY'),

Date(Date#(Left(filetime(), 9),'MMDDYYYY'),'MM/DD/YYYY')

and to add 2 days:

Date(Date#(Left(filename(), 9),'MMDDYYYY') + 2,'MM/DD/YYYY'),

Date(Date#(Left(filetime(), 9),'MMDDYYYY') + 2,'MM/DD/YYYY')

Re: Date

tamilarasu — Tue, 22 Dec 2015 03:56:42 GMT

Try,

Why are you using 9 in left function. You need to take 8 characters in the above case. Try

Date(Date#(Left(FileName(),8),'MMDDYYYY')+2,'DD/MM/YYYY')

Re: Date

Tue, 22 Dec 2015 04:02:08 GMT

Thank you soo much Sunny,

i missed it

now my out put is 12/09/2015

i have added two days for that,

Date(Date(Date#(left(filetime(),+9),'MMDDYYYY'),'MM/DD/YYYY')+2) it is coming as 11/12/2015 which is correct,

i want to change it to 12/11/2015 format, please suggest me how to do?

Re: Date

jagan — Tue, 22 Dec 2015 04:02:54 GMT

Hi,

Use 8 as second parameter in Left(), so that you will get only date part.

Date(Date#(left(filename(), 8),'MM/DD/YYYY'),'DD/MM/YYYY')

Hope this helps you.

Regards,

Jagan.

Re: Date

Tue, 22 Dec 2015 04:05:07 GMT

hi Tamil i want it in 12/11/2015 format instead of 11/12/2015,

please suggest

Re: Date

sunny_talwar — Tue, 22 Dec 2015 04:05:31 GMT

I think Jagan brings a good point, MMDDYYYY are 8 characters, so you probably need just 8 within the left function. and to change to DD/MM/YYYY, the date function should use that format:

Date(Date#(left(filetime(), 8),'MMDDYYYY') + 2,'DD/MM/YYYY')

Re: Date

tamilarasu — Tue, 22 Dec 2015 04:13:29 GMT

Simply interchange the Date format.

Date(Date#(Left(FileName(),8),'MMDDYYYY')+2,'DD/MM/YYYY')

Re: Date

Tue, 22 Dec 2015 05:21:53 GMT

Tamil,

it is coming as same there is no change if i mention MM/DD/YYYY or DD/MM/YYYY

Re: Date

tamilarasu — Tue, 22 Dec 2015 05:25:54 GMT

Sowmya, I just tried the below and it's showing correct date format.

It would be helpful, If you could post a sample file.?

Re: Date

Tue, 22 Dec 2015 05:33:35 GMT

Thanks a lot guys for Quick help

Re: Date

Tue, 22 Dec 2015 08:06:26 GMT

Hi Friends,

when i will take the date field individually as time it is coming perfectly, 12/11/2015

when i renames that as Date and combining with the other Dates format is changing. why like this? 11/12/2015

Re: Date

jagan — Tue, 22 Dec 2015 08:14:49 GMT

Hi,

Before combining you need to convert the dates to same format, then only it works.

Regards,

Jagan.

Re: Date

Tue, 22 Dec 2015 08:42:51 GMT

i am having below three date fields like...

IR:

1. 12/4/2015 12:00:00 AM

date(DataDate,'DD/MM/YYYY')as IRdate,

COA:

2. 20151209

Date(Date#(Left(FileName(),8),'YYYYMMDD')+2,'DD/MM/YYYY') as COAdate,

FX:

20151204

TRAILER (this field is containing string also)

Date( Date#(Date,'YYYYDDMM'),'DD/MM/YYYY') as FXdate,

when i concatenated these three fields as Date only COAdate format is changing

Re: Date

jagan — Tue, 22 Dec 2015 09:16:44 GMT

Hi,

Check what you are getting in below script

LOAD

Left(FileName(),8) AS Temp1,

Date#(Left(FileName(),8),'YYYYMMDD') AS Temp2

Re: Date

Wed, 23 Dec 2015 08:19:58 GMT

Hi Jagan,

Its working fine Thanks alot..

Re: Date

jagan — Wed, 23 Dec 2015 10:23:08 GMT

Mark appropriate answer as helpful so that it helps others.

Re: Date

Wed, 23 Dec 2015 10:34:55 GMT

HI Jagan,

i am facing one more issue here,

in one chart i want to show the entire data for all the dates.

and in one chart i would like to show only latest date data

how i can get this?

Re: Date

Wed, 23 Dec 2015 10:39:06 GMT

i have to get latest date for the below one.

Date(Date#(Left(FileName(),8),'MMDDYYYY')+2,'DD/MM/YYYY')

when i m trying max() above expression it is throwing error as invalid expression.

Re: Date

sunny_talwar — Wed, 23 Dec 2015 10:42:37 GMT

Are you doing that in the script? If yes than you won't be able to aggregate in the same table. You will probably need to create a resident load:

Table:

LOAD

Left(FileName(),8) AS Temp1,

Date#(Left(FileName(),8),'YYYYMMDD') AS Temp2

Max:

LOAD Date(Max(Temp2)) as MaxDate

Resident Table;