Inner join help

[mahitham]

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.

[tripatirao]

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

rtable:

load

a,

b,

metricid

from your path

innerjoin(rtable)

load

c,

d,

firstid&''&secondid  as metricid

from your path;

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

[YoussefBelloum]

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)

[mahitham]

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.

16

18

19

20

21

361

362

363

422

423

rf table

firstid contains

16

18

19

20

21

422

rf table second id contains

361

362

363

423

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

Thanks in advance.

[YoussefBelloum]

you mean this ?

r table:

r.metricid

16 

18 

19 

20 

21 

361

362

363

422

423

rf table:

firstid | second_id

16  361

18  362

19  363

20  422

21  423

[mahitham]

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

16 

18 

19 

20 

21 

361

362

363

422

423

inner join

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

16 

18 

19 

20 

21 

361

362

363

422

423

[HarishG · Accepted solution]

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

    21

    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;

[sasiparupudi1]

Try

table:

load

a,

b,

metricid

Resident yourtable

innerjoin(rtable)

load

c,

d,

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

from your path;

[YoussefBelloum]

Hi sasiparupudi1

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

[sasiparupudi1]

Hi youssefbelloum‌

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

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

Cheers