Transforming data while Loading from an Access table
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];
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.
Re: Transforming data while Loading from an Access table
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
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