topic Re: ApplyMap question in QlikView

ApplyMap question

Anonymous — Sun, 04 Oct 2015 03:55:25 GMT

can I use ApplyMap when there is only "1" common id between two db tables (no excel files)?

Table A (the facts table):

Contract_ID

Table B (table I am retrieving from):

Contract_ID, Faults

I want to retrieve the record "if Faults = 't' in table B. Can I do this or do I need to use Joins for this?

I have tried the following but it keeps looking for a file rather that recognising the tables.

Mapping table as follows:-

AtFault_MappingTable:

Mapping LOAD

training_contract_id,

[at_fault]

from training_contract_returns

where at_fault = 't';

Load -

AtFault:

LOAD training_contract_id,

at_fault,

ApplyMap('AtFault_MappingTable',training_contract_id,'Nofault') as [AtFaultContractReturns]

from training_contract_returns;

Re: ApplyMap question

Anonymous — Sun, 04 Oct 2015 13:39:11 GMT

yes laura, you can use applymap for any DB or Tables

Re: ApplyMap question

evan_kurowski — Sun, 04 Oct 2015 17:47:40 GMT

Laura Castagna wrote:

I want to retrieve the record "if Faults = 't' in table B. Can I do this or do I need to use Joins for this?

I have tried the following but it keeps looking for a file rather that recognising the tables.

Often when you feel certain you have defined all the tables in the data model, yet the script still says tables are missing, the issue is the unintentional auto-concatenation of one table into another.

When you comment out all ApplyMap portions of your code, and reload, do you see both Table A & Table B in the data model?

Re: ApplyMap question

md_qlikview — Sun, 04 Oct 2015 19:03:09 GMT

It is possible to use applymap in your case. Will you post some sample data?

Re: ApplyMap question

Gabriel — Sun, 04 Oct 2015 19:37:14 GMT

Hi Laura,

You can create composite key to match your ApplyMap to your table

If you can post sample file community will help with best answer

Re: ApplyMap question

Anonymous — Sun, 04 Oct 2015 19:48:36 GMT

Assuming you are trying to load from a database table then you need to add the SQL Select ? Otherwise it'll think you are trying to load from a file.

Re: ApplyMap question

Anonymous — Mon, 05 Oct 2015 00:29:58 GMT

thanks

Re: ApplyMap question

qlikviewwizard — Mon, 05 Oct 2015 01:50:17 GMT

Hi,

Please check these threads:

Don't join - use Applymap instead

quick applymap example

Re: applymap...help plssss

Re: ApplyMap question

Anonymous — Mon, 05 Oct 2015 04:26:39 GMT

Arjun,

do you have any examples where tables are used as i am receiving a syntax error?

Regards,

Laura

Re: ApplyMap question

Anonymous — Mon, 05 Oct 2015 04:47:42 GMT

so silly. i entered the Applymap into the select script instead of the load ! Doh!

Re: ApplyMap question

qlikviewwizard — Mon, 05 Oct 2015 05:00:33 GMT

Glad to hear this. Please mark Correct Answer and Helpful to close the thread.

Regards,

Arjun

Re: ApplyMap question

avinashelite — Mon, 05 Oct 2015 07:24:32 GMT

you can also try with the exist function too

Re: ApplyMap question

shair_abbas — Mon, 05 Oct 2015 07:29:02 GMT

hey.
I have a simple question.
What is the reason of using mapping while you can get the desire results by just using joins?

Re: ApplyMap question

avinashelite — Mon, 05 Oct 2015 07:33:09 GMT

when you have small amount of data their is not much difference between join and applymap but when you have huge data it has lot of impact with the performance . for eg say you need 3 to 4 columns from table 2 in table 1 , where table 1 have around 1 millions of records . In this case join is not ideal to use because it had huge impact on memory insted you can use the apply map

Re: ApplyMap question

avinashelite — Mon, 05 Oct 2015 07:34:42 GMT

check this thread

Joins Vs Applymap

Re: ApplyMap question

shair_abbas — Mon, 05 Oct 2015 08:16:30 GMT

As you say if you need 3 to 4 columns from table 2 in table1 then you have to create 3 to 4 mapping load because in mapping load you just have two value one for look up other is return value.
so if same table is loaded 4 time and searching in same table 4 time this will really harm the performance.

Although this is my opinion.
hope you find it helpful.

regards;

Syed Shair Abbas