Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rbecher
MVP
MVP

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

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

Astrato.io Head of R&D

View solution in original post

6 Replies
Gysbert_Wassenaar

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
rbecher
MVP
MVP

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

Astrato.io Head of R&D
ashfaq_haseeb
Champion III
Champion III

Hi,

try below.

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

Regards

ASHFAQ

Not applicable
Author

Hi Ashfaq,

This worked, thanks alot

rbecher
MVP
MVP

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

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

Astrato.io Head of R&D
Not applicable
Author

Hi Ralf,

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

Thanks,