topic Re: Applymap in Access database in QlikView

Applymap in Access database

Anonymous — Tue, 18 Nov 2014 15:53:27 GMT

Can we do an applymap to an Access database?

I've got the following code in Excel but i cant seem to convert it into access

(i'm using

ODBC

CONNECT32 TO [MS Access Database;DBQ=C:\Users\Desktop\QV\New Microsoft Access Database.accdb];

)

Map:

mapping load

if(FieldID = 6,DataID,) as DataID,
Data

FROM

(ooxml, embedded labels, table is Sheet1);

Customer_Name:

mapping load

if(FieldID = 5,DataID,) as DataID,
Data

FROM

(ooxml, embedded labels, table is Sheet1);

Seller:

mapping load

if(FieldID = 7,DataID,) as DataID,
Data

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD

DataID,
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
ApplyMap('Seller',DataID) as Seller

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD

ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
DataID

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Applymap in Access database

rustyfishbones — Tue, 18 Nov 2014 15:59:47 GMT

You need to use the Select button to select the table from the Access Database

Re: Applymap in Access database

Anonymous — Tue, 18 Nov 2014 16:02:13 GMT

When i try that though it complains i have a circular reference

ODBC

CONNECT32 TO [MS Access Database;DBQ=C:\Users\u22471\Desktop\QV\New Microsoft Access Database.accdb];

Map:

mapping select

if(FieldID = 6,DataID,) as DataID,
Data

FROM
[Sheet1];

Re: Applymap in Access database

Anonymous — Tue, 18 Nov 2014 16:05:37 GMT

This is what i have

ODBC

CONNECT32 TO [MS Access Database;DBQ=C:\Users\Desktop\QV\New Microsoft Access Database.accdb];

Map:

mapping sql select

if(FieldID = 6,DataID,) as DataID,
Data

FROM
[Sheet1];

Customer_Name:

mapping sql select

if(FieldID = 5,DataID,) as DataID,
Data

FROM

(ooxml, embedded labels, table is Sheet1);

Seller:

mapping sql select

if(FieldID = 7,DataID,) as DataID,
Data

FROM

(ooxml, embedded labels, table is Sheet1);

sql select

DataID,
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
ApplyMap('Seller',DataID) as Seller

FROM

(ooxml, embedded labels, table is Sheet1);

sql select

ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
DataID

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Applymap in Access database

rustyfishbones — Tue, 18 Nov 2014 16:13:10 GMT

Hi Paul,

I am not sure what you are trying to do here.

You have a connect statement to an access database, but yet you are not selecting any tables from the the DB

If you want to create a Mapping Load from the Access Database you need to Connect and then select the table you require, ensure you choose the fields, must be only two fields for a Mapping Load.

Below is an example where I have selected the Product Table and created a Mapping Table

You can then load the excel tables if you need them and add the ApplyMap part.

I hope that makes sense

Re: Applymap in Access database

Anonymous — Tue, 18 Nov 2014 16:20:36 GMT

So i cant applymap from an access database?

Re: Applymap in Access database

rustyfishbones — Tue, 18 Nov 2014 16:27:33 GMT

Yes you can.

You need to select a table from the Database and make sure you tick the box for Preceding load

Re: Applymap in Access database

oscar_ortiz — Tue, 18 Nov 2014 16:34:42 GMT

Paul,

You can applymap from an access database, you just have to use the proper syntax.

Map:

mapping

Load

if(FieldID = 6,DataID,) as DataID,

Data

;

sql select *
FROM
[Sheet1];

Customer_Name:

mapping

Load

if(FieldID = 5,DataID,) as DataID,

Data

;

sql select *
FROM

(ooxml, embedded labels, table is Sheet1);

Seller:
mapping

Load

f(FieldID = 7,DataID,) as DataID,

Data

;

sql select *
FROM

(ooxml, embedded labels, table is Sheet1);

FirstTable:

Load

DataID,

ApplyMap ('Map', DataID ) as Customer_Number,

ApplyMap('Customer_Name',DataID) as Customer_Name,

ApplyMap('Seller',DataID) as Seller

;

sql select *
FROM

(ooxml, embedded labels, table is Sheet1);

SecondTable:

Load

ApplyMap ('Map', DataID ) as Customer_Number,

ApplyMap('Customer_Name',DataID) as Customer_Name,

DataID

;

sql select *
FROM

(ooxml, embedded labels, table is Sheet1);

Good luck

Oscar

Re: Applymap in Access database

Anonymous — Tue, 18 Nov 2014 16:41:07 GMT

Thanks. I'm trying to remove excel out of the equation all together. I designed the original in excel to get the general idea and wanted to convert it to access. I wont be using excel at all, all the information is in access.