topic Re: problem with inner join in QlikView

problem with inner join

Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I'm problem to do a inner join sintaxe.

Example:

Table1

Id (field)

name (field)

id name

1 Mark

2 Jack

3 Luke

table2

Id (field)

type_device (field)

id type_device

1 laptop

1 Pc

2 laptop

2 Iphone

2 Ipad

3 laptop

I need a table with:

id name type_device

1 Mark laptop

1 Mark Pc

2 Jack laptop

2 Jack Iphone

2 Jack Ipad

3 Luke laptop

Can someone help me?

Re: problem with inner join

oknotsen — Fri, 24 Jun 2016 12:07:51 GMT

What you are showing is just some data, without the actual script you are using.

Could you tell us what your problem is with inner join, preferably together with the script you are using?

Re: problem with inner join

PrashantSangle — Fri, 24 Jun 2016 12:14:09 GMT

Hi,

what is your issue??

did you get any error

syntax

Load Id,name from table1;

inner join

Load Id,type_device from table2;

Regards

Re: problem with inner join

Anonymous — Fri, 24 Jun 2016 12:16:05 GMT

Hello,

I'd use applymap function, to do it:

Table1:

Mapping Load id,

name

from source; //use the correct syntax depending on the source

Table2:

Load id,

applymap('Table2', id) as name,

type_device

from source;

Re: problem with inner join

sunny_talwar — Fri, 24 Jun 2016 12:18:25 GMT

I see no issues with a simple inner join here:

Table1:

LOAD * Inline [

id, name

1, Mark

2, Jack

3, Luke

];

Inner Join (Table1)

LOAD * Inline [

id, type_device

1, laptop

1, Pc

2, laptop

2, Iphone

2, Ipad

3, laptop

];

Re: problem with inner join

oknotsen — Fri, 24 Jun 2016 12:20:25 GMT

Mapping is effectively a LEFT join, not an inner join.

It will also only return the first match if finds.

Re: problem with inner join

florentina_doga — Fri, 24 Jun 2016 12:21:02 GMT

try this

load * inline [id,

name

Mark

Jack

Luke];

inner join

load * inline [id ,type_device

laptop

Iphone

Ipad

laptop];

the result is

id	name	type_device
1	Mark	laptop
1	Mark	Pc
2	Jack	Ipad
2	Jack	Iphone
2	Jack	laptop
3	Luke	laptop

Re: problem with inner join

muthukumar77 — Fri, 24 Jun 2016 13:09:10 GMT

Hi,

You can use Left Join,

Table1:

LOAD * Inline [

id, name

1, Mark

2, Jack

3, Luke

];

Left Join (Table1)

LOAD * Inline [

id, type_device

1, laptop

1, Pc

2, laptop

2, Iphone

2, Ipad

3, laptop

];