topic Re: Change records in Access (accdb) load in QlikView

Change records in Access (accdb) load

Anonymous — Mon, 01 Sep 2014 07:57:30 GMT

Dear fellow Qlikviewers,

I've recently switched companies and am now working with MS Access in combination with Qlikview.

Currently I have the following piece of script

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Req_SRA01_Results.accdb];

SQL SELECT

`Account Manager`,

`Billed Weight`,

`Commercial product`,

`Legal Entity`,

`Origin Country`,

`Revenue Period`,

'SVE_Group'

if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?')))) as 'SVE_Group_2'

Tradelane,

FROM tblResults;

The problem arises with the bold part. I try to add an extra record based on a record available in the ACCDB.

However, when I try this, I get the following message:

SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''SVE_Group' if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?'))))'.

Am I doing something wrong? Clearly I am, but what?

Any help is appreciated.

Kind regards,

Stefan

Re: Change records in Access (accdb) load

ashfaq_haseeb — Mon, 01 Sep 2014 08:05:25 GMT

Hi,

Try below

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Req_SRA01_Results.accdb];

load

`Account Manager`,

`Billed Weight`,

`Commercial product`,

`Legal Entity`,

`Origin Country`,

`Revenue Period`,

'SVE_Group'

if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?')))) as 'SVE_Group_2'

Tradelane;

sql select *

FROM tblResults;

Regards

ASHFAQ

Re: Change records in Access (accdb) load

Anonymous — Mon, 01 Sep 2014 08:16:30 GMT

Works like a charm, thanks so much for the quick response as well!

Re: Change records in Access (accdb) load

ashfaq_haseeb — Mon, 01 Sep 2014 08:18:49 GMT

Welcome

Regards

ASHFAQ