Date

[#1]

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.

[#2]

Thanks a lot guys for Quick help

[#3]

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

[jagan]

Hi,

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

Regards,

Jagan.

[#5]

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

[jagan]

Hi,

Check what you are getting in below script

LOAD

*,

Left(FileName(),8) AS Temp1,

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

[#7]

Hi Jagan,

Its working fine Thanks alot..

[jagan]

Mark appropriate answer as helpful so that it helps others.

[#9]

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?

[#10]

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.

[sunny_talwar]

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;