Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date Issue (year missing)

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.

DayDateOpenHighLowCloseVolume
TuesdayJune 039,949.969,954.789,917.669,924.5225,249,588
FridayMay 309,926.739,970.779,924.639,943.27103,680,132
ThursdayMay 299,937.669,956.249,917.979,938.965,743,401
WednesdayMay 289,950.749,957.879,898.269,939.1772,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,

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Date Issue (year missing)

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

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

6 Replies
MVP & Luminary
MVP & Luminary

Re: Date Issue (year missing)

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.


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Re: Date Issue (year missing)

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)

Hi,

try below.

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

Regards

ASHFAQ

Not applicable

Re: Date Issue (year missing)

Hi Ashfaq,

This worked, thanks alot

MVP & Luminary
MVP & Luminary

Re: Date Issue (year missing)

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

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

Not applicable

Re: Date Issue (year missing)

Hi Ralf,

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

Thanks,