topic Re: Creating a Date from a number in QlikView

Creating a Date from a number

rsimson1 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I'm needing to create a date from (startdate) in the below script . The format of the (startdate) data coming out the database is pictured below. Can anyone help? I have put in the load script that I'm currently using to pull the start data as pictured.

COURSE:

LOAD

Coursecounter,

id as [Course ID],

category,

sortorder,

fullname,

shortname,

idnumber as [Course ID Number],

summary,

summaryformat,

format,

showgrades,

newsitems,

startdate,

marker,

maxbytes,

legacyfiles,

showreports,

visible,

visibleold,

groupmode,

groupmodeforce,

defaultgroupingid,

requested,

enablecompletion,

completionnotify,

cacherev

calendartype

FROM 10_MOODLE\Raw\ACMoodleCourse.QVD (QVD);

STORE COURSE INTO 10_MOODLE\Optimised\ACMoodleCourse.QVD (QVD);

Re: Creating a Date from a number

swuehl — Tue, 06 Sep 2016 15:53:44 GMT

I think your date field comes in Unix timestamp format (but I could be wrong):

LOAD

DayName(MakeDate(1970, 1, 1) + (startdate / 86400)) as NewStartDate,

startdate,

...

Re: Creating a Date from a number

Anonymous — Wed, 07 Sep 2016 08:12:36 GMT

Hi,

you can try:

COURSE:

LOAD

Coursecounter,

id as [Course ID],

category,

sortorder,

fullname,

shortname,

idnumber as [Course ID Number],

summary,

summaryformat,

format,

showgrades,

newsitems,

Date(startdate, 'YYYY-MM-DD') as StartDate,

startdate,

marker,

maxbytes,

legacyfiles,

showreports,

visible,

visibleold,

groupmode,

groupmodeforce,

defaultgroupingid,

requested,

enablecompletion,

completionnotify,

cacherev

calendartype

FROM 10_MOODLE\Raw\ACMoodleCourse.QVD (QVD);

STORE COURSE INTO 10_MOODLE\Optimised\ACMoodleCourse.QVD (QVD);