Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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)
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.
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
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
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;
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;
I think here you're missing something: IF(Len(TRim(firstid)) < number or > number or = number
Hi youssefbelloum
Yes,I wanted to write this..Thanks for point it out
IF(Len(Trim(firstid))>0,firstid , secondid) as metricid
Cheers