topic Re: Join ? in QlikView

Join ?

Wed, 07 May 2014 06:39:11 GMT

I have two tables , TableA & TableB

I want the all the records in TableA but exclude the common records in both the Tables.

Re: Join ?

its_anandrjs — Wed, 07 May 2014 06:54:12 GMT

Load table like this

TableA:

LOAD * Inline

[

];

TableB:

LOAD * Inline

[

] Where not Exists(C1,C2);

Re: Join ?

sujeetsingh — Wed, 07 May 2014 07:01:17 GMT

Table1:

LOAD * INLINE [

ID, Sales

1, 23

2, 4

3, 6

4, 9

5, 12

6, 9

];

Table2:NoConcatenate

LOAD * INLINE [

ID, Sales

1, 23

2, 4

13, 6

14, 9

7, 12

9, 9

];

Join(Table1)

LOAD

ID, Sales

,ID as ID2

Resident Table2;

DROP Table Table2;

MainData:NoConcatenate

LOAD

ID, Sales

Resident Table1 where IsNull(ID2)<>0 ;

DROP Table Table1;

Re: Join ?

jagan — Wed, 07 May 2014 07:02:53 GMT

Hi,

You can use Where not Exists() as Anand suggested, if not then attach some sample data and expected output.

Regards,

Jagan.

Re: Join ?

its_anandrjs — Wed, 07 May 2014 08:02:37 GMT

Load the tables like below script this will remove the duplicate records from your tables means common records and keep only uncommon records

Temp:

LOAD *,'Tab1' as Flag;

LOAD * Inline

[ID

F];

Join

TableB:

LOAD *,'Tab2' as Flag;

LOAD * Inline

[ID

E];

NoConcatenate

Temp2:

LOAD

ID,

Count(Flag) as Flag

Resident Temp

Group By ID

Order By ID;

Final:

LOAD

Resident Temp2

Where Flag=1;

DROP Tables Temp,Temp2;

Output you get

which is uncommon it both tables.

Re: Join ?

anbu1984 — Wed, 07 May 2014 09:28:51 GMT

In Sql

Select data from TableA excluding common records between TableA and TableB

Select col1,col2 from TableA

Minus

select col1,col2 from TableB

Re: Join ?

Wed, 07 May 2014 09:33:39 GMT

Hii,

Simply just use the function not Exist().

If not then you can try the other methods as well.