ApplyMap basic question

[#1]

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?

[NareshGuntur]

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

[#3]

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

[buzzy996]

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.

[#5]

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

[#6]

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.

jg

[#7 · Accepted solution]

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;

[jonathandienst]

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

[#9]

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!

[Anonymous]

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;