Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables, changes and SprintCalendar. Changes has ScheduledStartDate which is a full date/timestamp format. SprintCalendar has SprintDate which is just the date. If it were a timestamp, it would be for example, 11/30/2011 00:00:00 To link the fields, I need to convert the Changes.start_date to just the date, as in convertng 11/30/2011 16:30:30 to 11/30/2011 00:00:00.
I have tried DayName, Date#, DayStart, DayEnd, Day, Integer... You name it. Mostly I get an "Unsupported scalar function:day." error
My script is this:
SQL SELECT
start_date as ScheduledStartDate,
DayStart(start_date) as SprintDate < -- This is where it blows up. Some functions work, but not the one I want.
FROM changes;
// sprint calendar through 2013
LOAD SPRINTDATE as SprintDate,
SPRINTDAYNAME,
SPRINTWEEK,
SPRINTCODE
FROM
C:\QV_data\SprintCalendar.xlsx;
Help please - what can I try? I have searched the webs for any combination of QV, [source system], date, convert, etc.
Thanks...
I believe this:
LOAD *
,daystart(ScheduledStartDate) as SprintDate;
SQL SELECT
number as change_number
,start_date as ScheduledStartDate
FROM changes;
But it's hard to avoid all possible errors when just typing in a text window.
Are you doing a preceding load before your SQL statement? The preceding load allows you to use the qlikview functions. For instance:
Load
start_date
Date(start_date,'MM/DD/YYYY') as SprintDate;
SQL SELECT
start_date
From Changes;
Thanks mz, but it's not working.
I didn't know about using a preceding load.
I got the single field to load ok, but the rest of the table is missing now
I added another field to see what else came across.
LOAD Date(start_date,'MM/DD/YYYY') as SprintSchedDate;
SQL SELECT
number as change_number, < -- this field doesn't show up
start_date < -- this field doesn't show up either, and I couldn't rename it or else the first LOAD statement didn't work
FROM changes;
LOAD SPRINTDATE as SprintSchedDate,
SPRINTDAYNAME,
SPRINTWEEK,
SPRINTCODE
FROM
C:\QV_data\SprintCalendar.xlsx;
Ready for the next thing to try...
you need to qualify all of the fields in your select in the preceding load. You can do a lot with the preceding load I think you will really find it useful. Example
Load
a,
b,
c;
SQL Select
a,b,c
from table;
I believe this:
LOAD *
,daystart(ScheduledStartDate) as SprintDate;
SQL SELECT
number as change_number
,start_date as ScheduledStartDate
FROM changes;
But it's hard to avoid all possible errors when just typing in a text window.
That's it! Thanks!
After making all the corrections to re-identify all the field names I genericized for this posting...