topic Re: extracting year, month, day and time form sql load script in QlikView

extracting year, month, day and time form sql load script

dickelsa — Wed, 09 Oct 2013 12:49:53 GMT

Hi all,

I am currently working on something with an script that loads data from an SQL server.

here is a part of the code:

OLEDB CONNECT TO [sql script id];

SQL SELECT * FROM QV1.dbo."newbase_task_template_line_v";

SQL SELECT * FROM QV1.dbo."newbase_holding_v";

SQL SELECT * FROM QV1.dbo."newbase_ticket_strip_v";

SQL SELECT * FROM QV1.dbo."newbase_hrm_header_v";

SQL SELECT * FROM QV1.dbo."newbase_time_registration_v";

Now, when the script loads the data, it çreates fields for the data in above standing files.

For example, the file named newbase_time_registration has fields : 'start time'; 'end time'; 'ammount of hours spent'; etc

The time and date however are not listed in fields 'month', 'year', 'week', 'day' but are all standing in one field combined.

This is very annoying because then you would get a field value that looks like this:

Fri May 18 2012 00:00:00 GMT+0200 (CEST)

My question is: how do I extract the month, day, time and year from this?

normally you can put

Year('date')=Year

but since you cannot see the field headers in the script, it seems a little harder than that.

Any suggestions? please let me know

Thanks in advance

Kind regard

Dick

Re: extracting year, month, day and time form sql load script

Gysbert_Wassenaar — Wed, 09 Oct 2013 13:11:43 GMT

You will need to know the field names to be able to process them further. Once you know those you can use preceding loads the create the extra fields:

load *,

year([start date]) as Year,

month([start date] as Month,

day([start date]) as Day,

...etc for other dates

load *,

date#(mid([start time],5,11),'MMM DD YYYY') as [start date];

time#(mid([start time],17,8),'hh:mm:ss') as [start time];

... etc for other dates

SQL SELECT * FROM QV1.dbo."newbase_time_registration_v";

Re: extracting year, month, day and time form sql load script

saumyashah90 — Wed, 09 Oct 2013 13:12:47 GMT

you can consider Date from now then you can use

Load Date(Today()) as DateField

Re: extracting year, month, day and time form sql load script

dickelsa — Wed, 09 Oct 2013 13:36:19 GMT

Thanks! I think this will do the trick.

Do you know know how to extract the time also?

Re: extracting year, month, day and time form sql load script

Gysbert_Wassenaar — Wed, 09 Oct 2013 13:43:25 GMT

Look again

time#(mid([start time],17,8),'hh:mm:ss') as [start time];

Re: extracting year, month, day and time form sql load script

dickelsa — Wed, 09 Oct 2013 13:49:20 GMT

o sorry, I missed that, shame on me 😜

thanks!

Re: extracting year, month, day and time form sql load script

dickelsa — Fri, 18 Oct 2013 09:00:11 GMT

it does not work actually..