topic replacing a sql function in a load script to convert date in QlikView

replacing a sql function in a load script to convert date

Mon, 28 Oct 2013 21:18:43 GMT

I have a sql function that converts a datetime field to provide me MM-DD, the function is below:

CREATE FUNCTION func_convert_date( @in_date DATETIME )

RETURNS NVARCHAR(255)

BEGIN

DECLARE @str_date nvarchar(255);

SET @str_date = ( SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MONTH, @in_date)), 2) + '-' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(DAY, @in_date)), 2) );

RETURN @str_date;

END;

Im trying to apply this to a load script, or find an alternative method to convert a datetime into MM-DD. Is there a way to do this in the load script?

Thanks in advance

Re: replacing a sql function in a load script to convert date

jaimeaguilar — Mon, 28 Oct 2013 22:19:56 GMT

Hi,

the equivalent in QlikView script would be something like

Date(Date#(DateField, ' Put here the original format of the date field eg. YYYYMMDD, DD/MM/YYYY '), ' Put here the desired format, in this case MM-DD ')

so the code would be:

Load

Field1,

Field2,

Date(Date#(DateField, 'YYYYMMDD'), 'MM-DD') as DateName;

SQL Select *

FROM Table;

Regards

Re: replacing a sql function in a load script to convert date

jagan — Tue, 29 Oct 2013 02:21:56 GMT

Hi,

Try like thi

Data:

Load

Date(Date#(DateDimensionName, 'DateFormat'), 'MM-DD') as Date;

SQL Select *

FROM Table;

Replace DateDimensionName with your date field, and DateFormat with the format specifiers of your datefield (MM/DD/YYYY etc).

Regards,

Jagan.

Re: replacing a sql function in a load script to convert date

Tue, 29 Oct 2013 10:13:03 GMT

Thanks guys! this 'almost' works.. The reload of the script runs, I see the field but I get an empty list box. All the other fields from the table appear, so Im sure theres just one small thing Im missing in this function. My load statement is below.

Data:

Load

Date(Date#(BIRTHDATE, 'YYYY-MM-DD'), 'MM-DD') as Birthday;

SQL Select *

FROM TABLE;

The BIRTHDATE field is DATETIME format, would that make a difference?

Regards,

Grant

Re: replacing a sql function in a load script to convert date

Tue, 29 Oct 2013 10:37:17 GMT

I got it guys! Floor worked like a charm

Data:

Load

Date( Floor( BIRTHDATE ), 'MM-DD') as Birthday;

SQL Select *

FROM TABLE;

Thanks for your help, shot me in the right direction

Regards,

Grant