topic Re: Re: left join Substitute in QlikView

left join Substitute

Mon, 15 Sep 2014 10:36:06 GMT

Is there is any substitute of left join for the table having more than one fields

Re: left join Substitute

ashfaq_haseeb — Mon, 15 Sep 2014 10:37:38 GMT

you can create composite key

Regards

ASHFAQ

Re: left join Substitute

MayilVahanan — Mon, 15 Sep 2014 10:40:08 GMT

Create a composite key if you have more than one common field between 2 tables.

PID1 &'-'&PID2 as %Key

If the key fields is large length, then use Autonumber function for create the Key fields.

Re: left join Substitute

Mon, 15 Sep 2014 10:44:47 GMT

then?

Re: left join Substitute

ashfaq_haseeb — Mon, 15 Sep 2014 10:47:52 GMT

Create the same key in both tables and join them

Regards

ASHFAQ

Re: Re: left join Substitute

Mon, 15 Sep 2014 11:47:08 GMT

it will change the out put

see the attachment ,it will give different output if i used left join and if i used company key

Re: Re: Re: left join Substitute

ashfaq_haseeb — Mon, 15 Sep 2014 12:04:20 GMT

Hi,

Check this

Regards

ASHFAQ

Re: Re: Re: left join Substitute

ashfaq_haseeb — Mon, 15 Sep 2014 12:09:49 GMT

Hi use this

T1:
LOAD ID &'-'& NAME as key,
SECTION,
NAME
 ;
LOAD * Inline [
ID,NAME, SECTION
1,anu,D
2,ankita,B
3,ekta,A
4,Shweta,C
];


left join 
T2:
load ID &'-'& NAME as key,
%
;
LOAD * Inline [
ID ,NAME,%
3,shialy,00
4,shruti,01
5,kirt,8
6,Stuti,.5
3,ekta,90
];

Regards

ASHFAQ

Re: Re: left join Substitute

Mon, 15 Sep 2014 12:30:30 GMT

That's I know but I dnt want to use left join

As use of it increase the execution time in my original Qvw file

Re: Re: left join Substitute

ashfaq_haseeb — Tue, 16 Sep 2014 07:23:03 GMT

Then use apply map instead.

Regards

ASHFAQ

Re: Re: left join Substitute

Tue, 16 Sep 2014 08:40:00 GMT

But in that case one table should have only two fiel which is to b e used in mapping load

Re: Re: Re: left join Substitute

ashfaq_haseeb — Tue, 16 Sep 2014 08:53:29 GMT

Like this,

have a look at attach application.

if it answer, make sure to close this thread.

Regards

ASHFAQ

Re: left join Substitute

Tue, 16 Sep 2014 09:10:07 GMT

No it won't work, if i Want to add ID it generate a result which is equivalent to right join

Re: left join Substitute

ashfaq_haseeb — Tue, 16 Sep 2014 09:16:07 GMT

I gave you working example without join.

You could say applymap is replacement of join.

Regards

ASHFAQ

Re: left join Substitute

deepakqlikview_123 — Tue, 16 Sep 2014 09:43:44 GMT

Hi Ashfaq,

Sorry but can you please tell me 1 thing,

Apply map is work as look up ref table.

Left join can give all records from first table with matching rec in second table.

Then how could be applymap will be replacement for left join.

Thanks,

Deepak

Re: left join Substitute

Tue, 16 Sep 2014 09:48:56 GMT

It won't work as a replacement of left join in all the scenario

Re: left join Substitute

ashfaq_haseeb — Tue, 16 Sep 2014 10:32:04 GMT

Hi Deepak,

I have implemented both Left Join and apply map in this post you can a look at once.

To clear concepts I would suggest you to go through this article from Henric.

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Regards

ASHFAQ

Re: left join Substitute

deepakqlikview_123 — Tue, 16 Sep 2014 10:37:13 GMT

Thanks Ashfaq

That is helpful for me.