Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
siepe1990
New Contributor II

Change records in Access (accdb) load

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

Tags (3)
1 Solution

Accepted Solutions

Re: Change records in Access (accdb) load

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

3 Replies

Re: Change records in Access (accdb) load

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

siepe1990
New Contributor II

Re: Change records in Access (accdb) load

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

Re: Change records in Access (accdb) load

Welcome

Regards

ASHFAQ

Community Browser