topic Change Date format in QlikView

Change Date format

tracycrown — Mon, 18 Jan 2016 19:39:19 GMT

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

Re: Change Date format

sunny_talwar — Mon, 18 Jan 2016 19:43:30 GMT

Try this:

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;

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

Amount

FROM

Test.xlsx

(ooxml, embedded labels, table is All);

Re: Change Date format

Mon, 18 Jan 2016 19:49:27 GMT

First you need to convert your Input date field with DATE# function and then use date functions.

Please try like below:

LOAD

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

Year(Date) AS Year,

Num(Month(Date),'00') AS Month,

Num(Day(Date),'00') AS Day

;

LOAD

Date#(Date,'YYYYMMDD') AS Date,

Amount

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

Re: Change Date format

maxgro — Mon, 18 Jan 2016 19:49:51 GMT

add an interpretation function (Date#) to your script to

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

and some other useful help on dates

Get the Dates Right

Re: Change Date format

tracycrown — Fri, 22 Jan 2016 04:55:08 GMT

Dear Sunny

The sum(Amount) is not working, please advise.

Thanks

Tracy

Re: Change Date format

tracycrown — Fri, 22 Jan 2016 05:07:45 GMT

Dear Dathu

Your method seems ok but it will not be correct for following data especially in Chart for October month (two bars instead of one). Also, please advise how to generate quarterly data.

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

Thank you

Tracy

Re: Change Date format

mightyqlikers — Fri, 22 Jan 2016 05:10:52 GMT

Dear Tracy,

Please find the below script

Directory;

LOAD Date,

date(MakeDate(Left(Date,4),Mid(Date,5,2),Right(Date,2)),'DD-MM-YYYY') as DT,

date(MakeDate(Left(Date,4),Mid(Date,5,2),Right(Date,2)),'MMM-YYYY') as MMMYYDT,

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

Amount

FROM

Test.xlsx

(ooxml, embedded labels, table is All);

Regards

$@M

Re: Change Date format

tracycrown — Fri, 22 Jan 2016 05:22:09 GMT

Dear Maxgro

Your method does not sort the month in ascending order.

MonthYear	sum(Amount)
Jan-2013	2000
Aug-2014	2389
Jun-2014	15000
Oct-2013	30000

Thank you

Tracy

Re: Change Date format

mightyqlikers — Fri, 22 Jan 2016 05:26:18 GMT

Try sorting by

sort month year by DT field

Regards
$@M.

Re: Change Date format

tracycrown — Fri, 22 Jan 2016 05:26:27 GMT

Dear Sam

YearMonth is not correct.

YearMonth	sum(Amount)
May-57014	2000
Nov-57016	30000
Feb-57043	15000
Sep-57043	2389

Re: Change Date format

mightyqlikers — Fri, 22 Jan 2016 05:32:00 GMT

PFA

Re: Change Date format

HirisH_V7 — Fri, 22 Jan 2016 06:43:42 GMT

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

Re: Change Date format

settu_periasamy — Fri, 22 Jan 2016 07:17:35 GMT

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);

Re: Change Date format

tracycrown — Fri, 22 Jan 2016 07:54:15 GMT

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

Re: Change Date format

tracycrown — Fri, 22 Jan 2016 08:05:43 GMT

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

Re: Change Date format

settu_periasamy — Fri, 22 Jan 2016 08:08:21 GMT

Hi,

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

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

Re: Change Date format

mightyqlikers — Fri, 22 Jan 2016 10:42:54 GMT

HI Tracy

what do you mean by 57014,57015...

i am not understanding that thing.

$@M

Re: Change Date format

PradeepReddy — Fri, 22 Jan 2016 11:02:38 GMT

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);

Re: Change Date format

maxgro — Fri, 22 Jan 2016 12:39:24 GMT

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);

Re: Change Date format

tracycrown — Sat, 23 Jan 2016 06:26:20 GMT

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