topic Re: Import external data - convert string into date format in QlikView

Import external data - convert string into date format

Thu, 24 Jul 2014 13:46:33 GMT

Hi every body.

I'm importing data from an external MS ACCESS data source. In MS ACCESS table I have a field YEAR (format number).

By importing data into QV via script I need to convert this data into date format 1.12. & YEAR.

How can I do this ?

Thanx in advance

Re: Import external data - convert string into date format

Greg_Williams — Thu, 24 Jul 2014 13:50:57 GMT

date(makedate(year, month, day),'MM-DD-YYYY')

Re: Import external data - convert string into date format

Thu, 24 Jul 2014 13:58:22 GMT

Thank you, Greg.

I tried

SQL SELECT .... date(makedate(YEAR,12,1),'DD.MM.YYYY') as MyDate ...

but no success 😞

Duski

Re: Re: Import external data - convert string into date format

Thu, 24 Jul 2014 14:00:23 GMT

Please try like:

Date#(1 &'.'& 12 &'.'& Date_Year,'DD.MM.YYYY')

Attached with an example.

Edit: BTW do this in the Preload script not in the SQL Select section.

Re: Import external data - convert string into date format

Greg_Williams — Thu, 24 Jul 2014 14:03:01 GMT

Use the wizard when importing, instead of straight SQL...it's much easier to manage...

Load

<field>,

<field2>,

Year,

date(makedate(YEAR,12,1),'DD.MM.YYYY') as MyDate

...

from source

Re: Import external data - convert string into date format

Thu, 24 Jul 2014 14:13:53 GMT

Thank you Ajay.

I tried

date#(1 &'.'& 12 &'.'& YEAR,'DD.MM.YYYY') - no success 😞

I tried

date#('1.12.'& YEAR,'DD.MM.YYYY') - no success too 😞

Duski

Re: Import external data - convert string into date format

Thu, 24 Jul 2014 14:23:25 GMT

Ok.You can try the following

Load the table from the data base and then store it into a qvd (Just load like 10 records with like 3 or 4 relevant fields)
Then drop the original table and now load this qvd and do the above mentioned function

Still if it doesn't work:

If you can attach that qvd we could try to post a qvw with a solution

Re: Import external data - convert string into date format

Greg_Williams — Thu, 24 Jul 2014 14:24:07 GMT

Sometimes I have found that you need the `YEAR` around the field names. Have you tried placing the grave accent (`) (shares same key as tilde~) around the field name?

Re: Import external data - convert string into date format

Fri, 25 Jul 2014 09:42:00 GMT

Thank you once more, Ajay,

inserting the formula into tle LOAD section helped 🙂

See you

Duski