topic Re: Transforming data while Loading from an Access table in QlikView

Transforming data while Loading from an Access table

Thu, 31 Jan 2013 12:19:55 GMT

Hi Everyone,

Might seem quite straightforward, but is there a way I can change data while loading from an access table? Currently I am trying to use expressions on data I am loading from an access table. but it shows up an as an error while I reload. If I dont use any functions, it reloads fine. Does qlikview not allow the use of functions while loading from an access table?

ODBC CONNECT TO [MS Access Database;DBQ=L:\7 Projects\2012\Qlikview\test DB.mdb];

Load Period,

if(SubField(Period,' ',1)='CW','CW','STUB') as Type,

date(if(Len(SubField(SubField(Period,' ',2),'/',2))=1,'0'&SubField(SubField(Period,' ',2),'/',2),SubField(SubField(Period,' ',2),'/',2))&'/'&if(Len(SubField(SubField(Period,' ',2),'/',1))=1,'0'&SubField(SubField(Period,' ',2),'/',1),SubField(SubField(Period,' ',2),'/',1))&'/'&Right(Period,4)) as Final

SQL SELECT *

FROM `RB_tbl_Weekly data`;

Essentially the expression above Takes a text like CW 1/25/2013 in Period and gives CW to Type and 25/01/2013 to Final

Ive tried the same script while loading from a test excel file that I created and it works perfectly (See attached). Would be great if someone can point out what I'm doing wrong.

Thanks

Re: Transforming data while Loading from an Access table

fdelacal — Thu, 31 Jan 2013 12:33:56 GMT

I have the same problem one time, if you can try with this

left(Period,2) x example

left adnd right

work fine...

may be its help you.

Regards.

Re: Transforming data while Loading from an Access table

Thu, 31 Jan 2013 13:40:47 GMT

Thanks for your reply.

Essentially what I want to do is change a field called period in my access table. Period is text field that shows the week of sales and if the sales figure is for the current week or the whole month. For example, last week, I will have multiple rows in the table with

CW 1/25/2013

and

STUB 1/25/2013

I have written expressions that convert Period into 'Type' that captures the CW/STUB and 'Final' that stores this date as a British date with zeroes. i.e 25/01/2013

I have attached a qlikview and an excel file with these sort of dates. My expressions work perfectly when i load from excel. (See script and attached qlikview file)

It would be fantastic if i could use the same expressions but just load the table from access instead

Thanks

Re: Transforming data while Loading from an Access table

fdelacal — Thu, 31 Jan 2013 14:22:40 GMT

Try with this.

if(left(Period,2)='CW',subfield(Period,' ',2),

if(left(Period,4)='STUB',subfield(Period,' ',2))) as FINAL,

if(left(Period,2)='CW',subfield(Period,' ',1),

if(left(Period,4)='STUB',subfield(Period,' ',1))) as TYPE,

Period;

hope it helps you.

REGARDS.

Re: Transforming data while Loading from an Access table

Thu, 31 Jan 2013 14:36:32 GMT

Thanks for that.

Once again, it does not Load when i try it with my access table.

Can you show me the remaining part of the code,i.e with the Load and select and connect statements?

My problem IS NOT the expression, that works fine for me. It is the fact that when i try it with an ACCESS table it gives me an error on reloading.

Re: Transforming data while Loading from an Access table

fdelacal — Thu, 31 Jan 2013 14:45:21 GMT

ODBC CONNECT TO [Odonto;DBQ=C:\Users\MATest1.mdb];

load

if(left(FOTO_DESCRIPCION,2)='CW',subfield(FOTO_DESCRIPCION,' ',2),

if(left(FOTO_DESCRIPCION,4)='STUB',subfield(FOTO_DESCRIPCION,' ',2))) as FINAL,

if(left(FOTO_DESCRIPCION,2)='CW',subfield(FOTO_DESCRIPCION,' ',1),

if(left(FOTO_DESCRIPCION,4)='STUB',subfield(FOTO_DESCRIPCION,' ',1))) as TYPE,

FOTO_DESCRIPCION;

SQL SELECT `FOTO_DESCRIPCION`,

`FOTO_ID`

FROM `B_PACI_ FOTOS`;

EXIT Script;

FOTO_DESCRIPCION is the same to period for you. to me it works.

hope now help you

Re: Transforming data while Loading from an Access table

Thu, 31 Jan 2013 15:27:01 GMT

Works perfectly! Thanks!

I was missing the "Exit Script;" at the very end!

Re: Transforming data while Loading from an Access table

fdelacal — Thu, 31 Jan 2013 15:45:09 GMT

i use exit script, because i have more table load after that, is not necessary.

Nice!! Thanks to you.