topic Re: Inner join help in QlikView

Inner join help

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement.

In my oracle query I have the inner join statement like below. How to replicate this join in Qlik.

inner join risk_factor rf on r.metricid in (rf.firstid , rf.secondid)

Means on r table and rf tables need to apply inner join

but from r table need to consider metricid field

from rf table need to consider two ids like firstid and secondid two fields.

How to apply inner join on Qlik with two ids from second table.

Thanks in advance.

Re: Inner join help

tripatirao — Mon, 16 Apr 2018 07:33:39 GMT

if your metricid 12 and firstid and secondid is 1 AND 2 then concatenate 2 field and apply inner join.

rtable:

load

metricid

from your path

innerjoin(rtable)

load

firstid&''&secondid as metricid

from your path;

or share some smaple data so that we will help you.

Re: Inner join help

YoussefBelloum — Mon, 16 Apr 2018 07:48:23 GMT

Hi,

like tripati said, I think you will have to create a composite key here.

But try to share some line to see your tables Structures (rf and r)

Re: Inner join help

mahitham — Mon, 16 Apr 2018 07:52:05 GMT

Hi Tripati & youssefbelloum,

Please find the below sample data in the these three fields

r table metricid contains firstid and second id records like below.

361

362

363

422

423

rf table

firstid contains

422

rf table second id contains

361

362

363

423

here concatenation need to apply vertically. Please help me on this.

Thanks in advance.

Re: Inner join help

YoussefBelloum — Mon, 16 Apr 2018 08:03:41 GMT

you mean this ?

r table:

r.metricid

361

362

363

422

423

rf table:

firstid | second_id

16 361

18 362

19 363

20 422

21 423

Re: Inner join help

mahitham — Mon, 16 Apr 2018 08:11:20 GMT

Hi Youssef,

I am new to this oracle command. Not sure here in (rf.firstid , rf.secondid) how it works.

inner join risk_factor rf on r.metricid in (rf.firstid , rf.secondid)

Here if we create composite key then r.metricid not matching with firstid | second_id records and inner join wont happened.

r table:

r.metricid

361

362

363

422

423

inner join

if firstid second id like below then inner join works with all ids.

361

362

363

422

423

Re: Inner join help

HarishG — Mon, 16 Apr 2018 08:16:15 GMT

Hi,

May be this should work to concatenate your second table vertically,

Table1:

LOAD * INLINE [

firstid,secondid

16, 361

18, 362

19, 363

20, 423

422

];

Table2:

Load *,

firstid&'|'&secondid as Key

Resident Table1;

Drop Table Table1;

Final:

NoConcatenate

Load *,

SubField(Key,'|',1) as Key2

Resident Table2;

Concatenate(Final)

Load *,

SubField(Key,'|',2) as Key2

Resident Table2;

Drop Table Table2;

Re: Inner join help

sasiparupudi1 — Mon, 16 Apr 2018 08:23:51 GMT

Try

table:

load

metricid

Resident yourtable

innerjoin(rtable)

load

IF(Len(TRim(firstid)),firstid , rf.secondid) as metricid

from your path;

Re: Inner join help

YoussefBelloum — Mon, 16 Apr 2018 08:28:36 GMT

Hi sasiparupudi1

I think here you're missing something: IF(Len(TRim(firstid)) < number or > number or = number

Re: Inner join help

sasiparupudi1 — Mon, 16 Apr 2018 08:37:51 GMT

Hi youssefbelloum‌

Yes,I wanted to write this..Thanks for point it out

IF(Len(Trim(firstid))>0,firstid , secondid) as metricid

Cheers

Re: Inner join help

YoussefBelloum — Mon, 16 Apr 2018 08:49:53 GMT

Firstid and second Id can't be like above, they are separate columns, right ?

can you put these columns side by side to see the content ?