topic Re: MS Access Import data issue in QlikView

MS Access Import data issue

Thu, 16 May 2013 10:30:12 GMT

hi all,

i am importting 2 tables from same MS access and doing left join and using iF statement , but giving error. see below syntax:

ODBC CONNECT To [MS Access Database; DBQ=path];

raw:

select

category,

[country of issue],

[Coll Type],

[buss code],

if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region

from table1;

left join

select

category,

value

from table2;

Gives error on:

-If statement syntax

-left join as ODBC Failed

please advise

thanks

Re: MS Access Import data issue

m_woolf — Thu, 16 May 2013 10:57:09 GMT

The IF is QlikView syntax and can't be used inside a SQL Select statement.

Do a preceding load and use the IF there.

Re: MS Access Import data issue

Thu, 16 May 2013 11:07:30 GMT

as i mentioned the import is from MS Acess, thus Load will not work. i have to use Select

please advise

Re: MS Access Import data issue

m_woolf — Thu, 16 May 2013 11:09:51 GMT

Something like this:

raw:

load

if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region;

select

category,

[country of issue],

[Coll Type],

[buss code],

from table1;

Re: MS Access Import data issue

Gysbert_Wassenaar — Thu, 16 May 2013 11:16:22 GMT

MS-Access has the iif construct. Maybe this works:

iif([buss code]='ufu', 'Europe',iif([buss code]='afu','Asia','')) as Region.

It's possible you need to use double quotes instead of single quotes.

Alternatively you can use a preceding load:

Table1

Load

category,[country of issue],[Coll Type],[buss code],

if([buss code]='ufu', 'Europe',if([buss code]='afu','Asia','')) as Region;

Select category,[country of issue],[Coll Type],[buss code]

from table1;

Why the left join fails is impossible to tell without more information. Do you also get an error if you load table two as a table like this:

Table2:

select category, value from table2;