topic ApplyMap basic question in QlikView

ApplyMap basic question

Wed, 15 Apr 2015 13:27:57 GMT

Hiya,

This is the first time I want to make use of ApplyMap instead of a JOIN. Below you can see I made up a table containing aircraftno_i and ac_registr. However there is no relation defined between these fields.

The relation is defined in the "aircraft" table:

Aircraft:

LOAD ac_registr,

ac_typ,

aircraftno_i

FROM DataBase.aircraft;

But I dont want to insert this table or make it join,I want to use the mapping function.

So now I want to map this ac_regsistr to aircraftno_i:

MapAircraftno2Ac_registr:

Mapping Load aircraftno_i, ac_registr FROM (HERE IS MY ISSUE);

So that I can ApplyMap in the MSC_MASTER table:

ApplyMap('MapAircraftno2Ac_registr', aircraftno_i, null()) as ac_registr,

My question is as follows, what do I input in the red marked area? I cannot select the "aircraft" table which contains the relation because I dont want it in my model (it would create syntetic table), I only want it to look up in that table.

Anyone?

Re: ApplyMap basic question

NareshGuntur — Wed, 15 Apr 2015 13:35:43 GMT

MapAircraftno2Ac_registr:

Mapping Load aircraftno_i, ac_registr FROM (HERE IS MY ISSUE);

That will be your data source.

Mapping table will be automatically dropped after the reload. It will not be in your data model.

Hope it is clear.

Cheers,

Naresh

Re: ApplyMap basic question

Wed, 15 Apr 2015 13:40:23 GMT

Dutchsky,

Mapping Load tables drop automatically at the end of the load, so you don't need to worry about the relation existing from the mapping load table you load.

So for your Red highlighted area it can be a table in your database that contains the fields. Then the applyMap you use will land in the load statement where you have it input. At the end of the load of all tables, the mark of the mapping table will indicate this table needs to be dropped and it will be dropped automatically.

The best way to test is to have two load statements. The first is the mapping load table, and the second is a table that will insert the value via apply map. Simple test should result in only one table in your model after load completes.

Hope this helps.

Jeff G

Re: ApplyMap basic question

buzzy996 — Wed, 15 Apr 2015 14:01:46 GMT

u can simply use ur table in read mark place,that wont be cause any synthetics key because ur using Mapping key work on ur mapping table that will drop that mapping table automatically.

Re: ApplyMap basic question

Wed, 15 Apr 2015 14:17:11 GMT

Could the script part be different for when using files or a database? It is not quite working yet, this is my exact script:

-----

ODBC CONNECT TO [Database];

Aircraft:

LOAD ac_registr,

ac_typ,

aircraftno_i;

SQL SELECT ac_registr,

ac_typ,

aircraftno_i

FROM Database.aircraft;

MapAircraftno2Ac_registr:

Mapping Load aircraftno_i, ac_registr FROM Database.aircraft;

------

When I remove the mapping part, it works just fine and it will create the aircraft table. Now it says it can't find the "file" while im not working with files at all. Ideas?

(my connection to the database is fine, all other things work properly except for this mapping script.)

Re: ApplyMap basic question

Wed, 15 Apr 2015 14:19:49 GMT

Hi,

Perform the mapping load first. Plus your code above does not show the applymap. Because you have the mapping load after any applymap you have before won't work.

I'll attach a sample example momentarily.

Re: ApplyMap basic question

Wed, 15 Apr 2015 14:20:58 GMT

Change the load for the mapping table to the below

MapAircraftno2Ac_registr:

Mapping Load

aircraftno_i,

ac_registr

;

SQL SELECT aircraftno_i, ac_registr FROM Database.aircraft;

Re: ApplyMap basic question

jonathandienst — Wed, 15 Apr 2015 14:26:09 GMT

You need a SQL SELECT in your mapping load, rather than a LOAD:

Mapping SQL SELECT aircraftno_i, ac_registr FROM Database.aircraft;

HTH

Jonathan

Re: ApplyMap basic question

Wed, 15 Apr 2015 14:29:34 GMT

Thats it, sounds very logical to me now! Thank you all for the comments, every single one was helpful! This will help me along in my graduate research

Qlikview and SQL is very new to me, but Im learning more day by day!

Re: ApplyMap basic question

Anonymous — Wed, 15 Apr 2015 14:29:51 GMT

The question is in fact not about mapping but about loading the data.

If from database, it is

SELECT.... FROM

if it is from a previously loaded table, it is

LOAD... RESIDENT

if it is from a file, it is

LOAD ... FROM

In your case probably the best way is

Mapping SELECT DISTINCT

aircraftno_i,

ac_registr

FROM Database.aircraft;

Re: ApplyMap basic question

Wed, 15 Apr 2015 14:30:15 GMT

Dutchsky,

see this sample. The other folks are correct as well. You can create a mapping load from a SQL query as well. You don't have to load the data first then create the mapping load.

/****************Mapping Load First*******************/

IUCR:

MAPPING LOAD IUCR,

[PRIMARY DESCRIPTION]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

/****************Main Table Second******************/

LOAD ID,

[Case Number],

Date,

Block,

IUCR,

/******************ApplyMap******************/

ApplyMap('IUCR', IUCR) as [Primary Description],

[Primary Type],

Description,

[Location Description],

Arrest,

Domestic,

Beat,

District,

Ward,

[Community Area],

[FBI Code],

[X Coordinate],

[Y Coordinate],

Year,

[Updated On],

Latitude,

Longitude,

Location

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where Date = 2008;