Transforming data while Loading from an Access table

[#1]

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

[fdelacal]

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.

[#3]

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

[fdelacal]

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.

[#5]

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.

[fdelacal · Accepted solution]

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

[#7]

Works perfectly! Thanks!

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

[fdelacal]

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

Nice!! Thanks to you.