topic Re: Diff b/w Left Join and Apply Map? in QlikView

Diff b/w Left Join and Apply Map?

Fri, 23 Jan 2015 06:11:40 GMT

Hi,

Any one can i explain with Left Join and Apply Map?

which is better performance in qv while creating data model?

Sub2u

Re: Diff b/w Left Join and Apply Map?

its_anandrjs — Fri, 23 Jan 2015 06:29:57 GMT

Hi,

If you use Mapping load then in Mapping table needs two fields at least for mapping weather as in joins need sigle or more common fields for joins.

Regards

Anand

Re: Diff b/w Left Join and Apply Map?

jagan — Fri, 23 Jan 2015 06:34:31 GMT

Hi,

Left join will join two tables physically like we do in normal SQL, where as by using ApplyMap() we can create a new column in the table based on the key values.

The difference is when you want to include multiple columns into the table then go for join, if you want to include only one column then use applymap().

Join Scenario:

Table1:

Columns : C1, C2, C3

Table2:

Columns: C1, C4, C5, C6

Result: C1, C2, C3, C4, C5, C6

Table1:

Columns : C1, C2, C3

Table2:

Columns: C1, C4

Result: C1, C2, C3, C4

Regards,

Jagan.

Re: Diff b/w Left Join and Apply Map?

its_anandrjs — Fri, 23 Jan 2015 06:41:19 GMT

Hi,

For more clear vision

1. Mapping Load

MapTab:

Mapping LOAD * Inline [

ID,Desc

1,Desc1

2,Desc2

3,Desc3

4,Desc4 ];

LOAD ApplyMap('MapTab',ID) as Desc ,* Inline [

ID,Sales,Location

1,48741,L1

2,12369,L2

3,14852,L3 ];

In this you get Desc column on the base of ID

2. With joins

MapTab:

LOAD * Inline

[

ID,Desc,Details

1,Desc1,some info

2,Desc2,other info

3,Desc3,related info

4,Desc4,info given

];

Left Join

LOAD * Inline

[

ID,Sales,Location

1,48741,L1

2,12369,L2

3,14852,L3

];

Then you get 5 fileds single table

Regards

Anand

Re: Diff b/w Left Join and Apply Map?

Fri, 23 Jan 2015 07:10:45 GMT

there is one another difference between join and applymap is in granularity,

hope above image make some clarity

Re: Diff b/w Left Join and Apply Map?

Colin-Albert — Fri, 23 Jan 2015 07:21:03 GMT

I would suggest you avoid using join as far as possible and use applymap instead, even where several applymap are required. Joins may cause data to be inadvertently duplicated and can be slow during reloads.

See the blog below by hic for more details.

Don't join - use Applymap instead

Re: Diff b/w Left Join and Apply Map?

Fri, 23 Jan 2015 09:22:08 GMT

Hi!

In single mapping, performance is almost the same for both methods. But if there are a lot of fields to map in one table, better are ApplyMap functions.

Other differences of methods, as mentioned Colin Albert, is that in "left join" both tables affected on record level - number of records of left table may grow and/or double records both tables may be withdraw. ApplyMap do not affects records at all.

Re: Diff b/w Left Join and Apply Map?

Fri, 23 Jan 2015 11:26:11 GMT

Thank you all.

Thanks for your comments.

ApplyMap and Lookup .. which is the better one to improve performance in data model?

Thanks in advance.

Sub2u

Re: Diff b/w Left Join and Apply Map?

hic — Fri, 23 Jan 2015 13:14:06 GMT

Applymap() is much faster than Lookup().

HIC