topic difference between two table in QlikView

difference between two table

omid5ive — Sun, 11 Oct 2015 06:56:11 GMT

i have two table like below :

A
1
2
3

B
1
3
4
5

i want two have difference of this two table like blow:

C
4
5

how can i do this?

Re: difference between two table

Kushal_Chawda — Sun, 11 Oct 2015 07:07:56 GMT

TabA:

Load A

From TableA;

TabB:

Load B

From TableB

where not exist(A,B);

Drop table TabA;

Re: difference between two table

omid5ive — Sun, 11 Oct 2015 07:39:18 GMT

i attached my file with your solution but it cant show anything

Re: difference between two table

petter — Sun, 11 Oct 2015 08:10:13 GMT

You have to fix your load script and write this:

WHERE Not( Exists( 'Factor_Number1' , number ) ) ;

You were missing paranthesis ( ) for the Not()-function and you have to quote the field name 'Factor Number1' with single quotes...

Re: difference between two table

petter — Sun, 11 Oct 2015 08:12:04 GMT

Re: difference between two table

omid5ive — Sun, 11 Oct 2015 08:30:45 GMT

i think i cant use not exist with auto generate command.

is it true?

cause when create A table in excel i can get correct answer with your solution

Re: difference between two table

petter — Sun, 11 Oct 2015 08:34:31 GMT

QlikView when it performs the Exists()-function does not care if the field it checks against (the first parameter) is originally populated by and AutoGenerate. The AutoGenerate populates a table with one or more fields and these fields are on the following script statements first-class fields without any restrictions or limitations compared to any other fields which comes from other sources...

So the answer must be ... the problem is elsewhere

Re: difference between two table

omid5ive — Sun, 11 Oct 2015 08:34:46 GMT

sorry

can you check my attached document?

Re: difference between two table

petter — Sun, 11 Oct 2015 08:44:36 GMT

Your code is working as expected in my eyes. The AutoGenerate makes an exclusion list of ten numbers from 1 to 10 and the Excel file is containing numbers within that range so nothing will be let through the "filter".

If you change one or more of the values to be outside the range 1..10 then you will see that it works - and works as expected. At least what I thought you expect from what you told us.

Re: difference between two table

petter — Sun, 11 Oct 2015 08:45:56 GMT

Here are updated attachements

Re: difference between two table

omid5ive — Sun, 11 Oct 2015 08:52:40 GMT

sorry if i disturb you but in excel file we dont have number 1 but in A list i cant see 1 i just see 40 and 70

Re: difference between two table

petter — Sun, 11 Oct 2015 09:00:49 GMT

Your orginal post actually showed a pure filtering in the example you posted. Although your title may says something else...

So what you really need is a table which contains everything that is not in one or the other table - right?

Re: difference between two table

omid5ive — Sun, 11 Oct 2015 09:07:06 GMT

i have two table with same values

nobody cant delete any values from first table

but in some case i need to delete some values from second table

i need this deleted values. it means that i need the values from first table that they are not exist in second table

Re: difference between two table

petter — Sun, 11 Oct 2015 09:21:59 GMT

Flip the order of the two tables and then the Not( Exists( .... logic can work as you want it to:

C:
LOAD A 
FROM
factor.xlsx
(ooxml, embedded labels, table is Sheet1)
;

A:
LOAD
  B 
WHERE Not( Exists( 'A' , B ) )
;
LOAD (RecNo() + 0) AS B
AUTOGENERATE 10
 ;

Re: difference between two table

omid5ive — Sun, 11 Oct 2015 09:28:40 GMT

thank you petter i got my answer with your perfect help

Re: difference between two table

petter — Sun, 11 Oct 2015 09:29:47 GMT

My Pleasure

Re: difference between two table

nizamsha — Sun, 11 Oct 2015 09:32:31 GMT

TableA:

LOAD * Inline [

];

LOAD * Inline [

]Where not Exists(A,B);

DROP Table TableA;