Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

Inner join help

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.

1 Solution

Accepted Solutions
HarishG
Partner
Partner

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;

Key2.png

View solution in original post

10 Replies
tripatirao
Creator II
Creator II

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
Champion
Champion

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
Creator II
Creator II
Author

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
Champion
Champion

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
Creator II
Creator II
Author

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
Partner
Partner

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;

Key2.png

sasiparupudi1
Master III
Master III

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
Champion
Champion

Hi sasiparupudi1

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

sasiparupudi1
Master III
Master III

Hi youssefbelloum

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

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


Cheers