7 Replies Latest reply: Jan 31, 2013 10:45 AM by facundo de la cal RSS

    Transforming data while Loading from an Access table

      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