Change Date format

[tracycrown]

Dear Sir/Madam

Kindly advise how to convert report date YYYYMMDD to following formats in scrip :

1. MMM-YY

2. YYYY

3. MM

4. DD

Thank you

Tracy

[mightyqlikers]

PFA

[HirisH_V7]

Hi,

Check this,

Data:
LOAD       
  Mid(Date,7,8)& '/'& TRim(Left(mid(Date,5,6),2)) & '/'& Mid(Date,1,4) as Date,
  Amount

FROM
[Test (1).xlsx]
(ooxml, embedded labels, table is All);

Front End:

HTH,

PFA,

Hirish

[settu_periasamy]

Hi,

sunindia‌ suggestion working for me.  You just try to add the Amount Field in your Preceding Load. see below

Table:

   LOAD Date,

  Date(Date, 'MMM-YY') as MonthYear,

  Year(Date) as Year,

  Month(Date) as Month,

  Num(Month(Date)) as MonthNum,

  Day(Date) as Day

Amount;

LOAD Date#(Date,'YYYYMMDD') as Date,

    Amount

FROM

Test.xlsx

(ooxml, embedded labels, table is All);

[tracycrown]

Dear Settu

Please advise why Oct-2013 (MMM-YYYY) did not group into one.

MonthYear	Amount
Feb-2013	4,200
Jul-2013	2,500
Aug-2013	2,000
Oct-2013	30,000
Oct-2013	295
Feb-2014	2,389
Jul-2014	15,000

Thank You

Tracy

[tracycrown]

Dear Sam

Why the Year (57014) in YearMonth is not correct ?.

Report :

YearMonth	sum(Amount)
Aug-57014	4200
Dec-57015	2500
Apr-57016	2000
Nov-57016	30000
Nov-57016	295
Jan-57042	2389
May-57043	15000

Data :

Date	Amount
20130802	2000
20131012	30000
20130701	2500
20130215	4200
20131018	295
20140703	15000
20140219	2389

Thank you

Tracy

!

[settu_periasamy]

Hi,

is that table box or Chart, Look at the below screen shot..

Attached the sunindia‌ file with 'Amount' Field added..

[mightyqlikers]

HI Tracy

what do you mean by 57014,57015...

i am not understanding that thing.

$@M

[PradeepReddy]

Try like this.. it is working fine for me.. This is same as sunindia , just added the Amount field..

Load Date_Temp as Date,

  Date(Date_Temp,'MMM-YYYY') As 'MMM-YYYY',

  Year(Date_Temp) as 'YYYY',

  Month(Date_Temp) As 'MM',

  Day(Date_Temp) As 'DD',

  Amount;

LOAD Date,

     Date#(Date,'YYYYMMDD') as Date_Temp,

     Amount

FROM Test.xlsx (ooxml, embedded labels, table is All);

[maxgro]

In my test it sorts, this is the chart

and this the script

Directory;

LOAD Date,

     //Date(Date,'MMM-YYYY') as YearMonth,

     Date(Date#(Date, 'YYYYMMDD'),'MMM-YYYY') as YearMonth,

     Amount

FROM

Test.xlsx

(ooxml, embedded labels, table is All);

[tracycrown]

Dear Settu

Not sure why my result in Oct-13 is different from your screen shot, please refer to attached files and advise.

Thank you

Tracy