topic Re: Joining the records in QlikView

Joining the records

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

Hi,

As i am new to qlikview, i have basic question about joins..

Table A
Joining field
1
2
3
4
5
6
7

Table B

Joining field

Output table should be
Joining field
1
2
3
4
5
6

Which kind of join i can use to get this output? or any function?

Re: Joining the records

sunny_talwar — Thu, 16 Mar 2017 09:04:43 GMT

May be Inner Join on Joining field

Re: Joining the records

sridhar_sigired — Thu, 16 Mar 2017 09:16:07 GMT

Thanks Sunny... inner join is giving result as 7.. which is common

Re: Joining the records

sunny_talwar — Thu, 16 Mar 2017 09:19:28 GMT

My bad, you are right... May be Where not Exists instead of join

TableB:

LOAD Joining Field,

....

FROM TableB;

TableA:

LOAD Joining Field,

...

FROM TableA

Where Not Exists(Joining Field);

Left Join(TableA)

LOAD *

Resident TableB;

DROP Table TableB;

Re: Joining the records

ramasaisaksoft — Thu, 16 Mar 2017 09:20:17 GMT

then do inner join ,later drop the field

drop field fieldName(your case 7);

Re: Joining the records

neha_shirsath — Thu, 16 Mar 2017 09:21:56 GMT

Hi Sridhar,

Your output seems to be data from left table.

So, may be left join (Table A left join Table B)will give you the required output.

Thanks,

Neha

Re: Joining the records

Thu, 16 Mar 2017 09:31:28 GMT

Hi,

this logic worked fine for me and it seems simple

TableB:

LOAD * INLINE [

JoinTable

10];

TableA:

right join

LOAD * INLINE

[

JoinTable,

]

Where not Exists(JoinTable)

hope this helps

thanks

Re: Joining the records

muthukumar77 — Thu, 16 Mar 2017 09:33:05 GMT

Try this,

T1Temp:

LOAD F1 as F1Temp;

LOAD * INLINE [

];

Inner Join(T1Temp)

LOAD F1 as F1Temp;

LOAD * INLINE [

];

Final:

LOAD * INLINE [

] Where not Exists(F1Temp,F1);

Concatenate(Final)

LOAD * INLINE [

]Where not Exists(F1Temp,F1);

DROP Table T1Temp;

Re: Joining the records

rahulpawarb — Thu, 16 Mar 2017 09:38:37 GMT

Hello Sridhar,

Trust that you are doing great!

Please refer given sample script:

[Table A]:
LOAD * INLINE [
Joining field
1
2
3
3
3
4
5
6
7
];
LEFT JOIN 
[Table B]:
LOAD *, RowNo() AS RowNo INLINE [
Joining field
3
3
7
8
]
;


TableA:
LOAD DISTINCT [Joining field]
Resident [Table A]
Where Len(RowNo) = 0;


DROP Table [Table A];

Hope this will be helpful.

UPDATED: Modified the script as well as application to get desired result in case of duplicates in data set.

Regards!

Rahul

Re: Joining the records

sridhar_sigired — Thu, 16 Mar 2017 09:39:34 GMT

Thank you all.... unfortunately the field value may duplicate something like this.

Table A
Joining field
1
2
3
3
3
4
5
6
7

Table B

Joining field

Output table should be

Joining field

I cannot make any composite in my case... so would it be possible to get result using above? It is just sample data...

Re: Joining the records

neha_shirsath — Thu, 16 Mar 2017 09:51:33 GMT

Hi,

Try below code-

TableB:

LOAD * Inline [

Joining field

];

TableA:

LOAD [Joining field] as test Inline [

Joining field

];

NoConcatenate

TableAB:

Load test as final Resident TableA

Where not Exists([Joining field],test);

Re: Joining the records

tyagishaila — Thu, 16 Mar 2017 09:52:49 GMT

TableB:

LOAD * Inline [

JoiningField

];

NoConcatenate

TableA:

LOAD * Inline [

JoiningField

]where Not Exists(JoiningField) ;

DROP Table TableB;

Re: Joining the records

Thu, 16 Mar 2017 09:54:05 GMT

Hi,

then try the load distinct

thanks,

Re: Joining the records

muthukumar77 — Thu, 16 Mar 2017 09:54:51 GMT

So Try like this,

T1Temp:

LOAD F1 as F1Temp;

LOAD * INLINE [

];

Inner Join(T1Temp)

LOAD F1 as F1Temp;

LOAD * INLINE [

];

Concatenate(T1Temp)

LOAD F1 as F1Temp;

LOAD * INLINE [

] Where Not Exists(F1Temp,F1);

Final:

LOAD * INLINE [

] Where not Exists(F1Temp,F1);

Concatenate(Final)

LOAD * INLINE [

]Where not Exists(F1Temp,F1);

DROP Table T1Temp;

Re: Joining the records

neha_shirsath — Thu, 16 Mar 2017 09:55:19 GMT

Hi,

Please find attached file.

Hope this will help you.

Thanks,

Neha