topic Re: Date Issue (year missing) in QlikView

Date Issue (year missing)

Tue, 03 Jun 2014 11:37:09 GMT

Hi guys,

I've got an issue with some date's, I'm trying to track some daily share index prices (data format shown below)

It is missing a year value's and I need to make this into a date format of DD/MM/YY, this goes as far back as 2013.

Day	Date	Open	High	Low	Close	Volume
Tuesday	June 03	9,949.96	9,954.78	9,917.66	9,924.52	25,249,588
Friday	May 30	9,926.73	9,970.77	9,924.63	9,943.27	103,680,132
Thursday	May 29	9,937.66	9,956.24	9,917.97	9,938.9	65,743,401
Wednesday	May 28	9,950.74	9,957.87	9,898.26	9,939.17	72,748,507

I'm also looking to get a percentage change of the closing price, but thought I'd need to get the date right first.

I'm relatively new to scripting, but I can't seem to crack this.

Thanks,

Re: Date Issue (year missing)

Gysbert_Wassenaar — Tue, 03 Jun 2014 11:50:23 GMT

Probably easiest to clean it up in excel first. Or first load the data from 2013 and the data from 2014 in a second load. That way you can create the date field more easily with something like date#( Date & ' 2014', 'MMMM DD YYYY') as MyDate.

Re: Date Issue (year missing)

rbecher — Tue, 03 Jun 2014 11:58:00 GMT

Hi Patrick,

you can try something like this:

Data:

LOAD Day, Date(Date#(Date & ' 2013', 'MMMM DD YYYY')) as Date, Open, High, Low, Close, Volume;

LOAD * INLINE [

Day, Date, Open, High, Low, Close, Volume

Tuesday, June 03, "9,949.96", "9,954.78", "9,917.66", "9,924.52", "25,249,588"

Friday, May 30, "9,926.73", "9,970.77", "9,924.63", "9,943.27", "103,680,132"

Thursday, May 29, "9,937.66", "9,956.24", "9,917.97", "9,938.9", "65,743,401"

Wednesday, May 28, "9,950.74", "9,957.87", "9,898.26", "9,939.17", "72,748,507"

];

Result:

NOCONCATENATE LOAD *, 1 - (Close/Previous(Close)) as ChangePct

Resident Data

Order By Date;

Drop Table Data;

- Ralf

Re: Date Issue (year missing)

ashfaq_haseeb — Tue, 03 Jun 2014 12:01:50 GMT

Hi,

try below.

date(MakeDate(2013,num(month(date#(Date,'MMM DD'))),right(Date,2)),'DD/MM/YYYY')

Regards

ASHFAQ

Re: Date Issue (year missing)

Tue, 03 Jun 2014 12:56:36 GMT

Hi Ashfaq,

This worked, thanks alot

Re: Date Issue (year missing)

rbecher — Tue, 03 Jun 2014 13:00:47 GMT

..this is more simple, readable and much better to understand:

Date(Date#(Date & ' 2013', 'MMMM DD YYYY'))

Re: Date Issue (year missing)

Tue, 03 Jun 2014 13:11:58 GMT

Hi Ralf,

You are correct, I was originally having some issues puting your method into my script, but it now works perfectly.

Thanks,