Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

pzwonford
New Contributor II

Applymap in Access database

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);


8 Replies
rustyfishbones
Honored Contributor II

Re: Applymap in Access database

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

pzwonford
New Contributor II

Re: Applymap in Access database


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];

pzwonford
New Contributor II

Re: Applymap in Access database

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);


rustyfishbones
Honored Contributor II

Re: Applymap in Access database

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

2014-11-18_1607.png

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

I hope that makes sense

pzwonford
New Contributor II

Re: Applymap in Access database

So i cant applymap from an access database?

rustyfishbones
Honored Contributor II

Re: Applymap in Access database

Yes you can.

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

2014-11-18_1626.png

oscar_ortiz
Valued Contributor

Re: Applymap in Access database

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

pzwonford
New Contributor II

Re: Applymap in Access database

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.

Community Browser