topic Re: how to subtract two tables in script level in QlikView

how to subtract two tables in script level

Srinivas — Tue, 02 Jun 2015 08:18:42 GMT

Hi All,

I have two tables both tables have same values, and I need to subtract to two tables to create another table in script level.see below tables.

BaseTable:

LOAD * INLINE [

C_ID, P_ID, B_ID, Name

1, 101, 50, abd

2, 102, 51, adf

3, 103, 52, fgff

4, 104, 53, egf

5, 105, 54, egf

6, 106, 55, edffff

7, 107, 56, deret

8, 108, 57, ffrt

9, 109, 58, gger

10, 110, 59, errg

11, 111, 60, erww

12, 112, 61, c

13, 113, 62, djj

14, 114, 63, jnkl

15, 115, 64, sdi

16, 116, 65, ljj

17, 117, 66, knj

18, 118, 67, kkji

19, 119, 68, mjh

20, 120, 69, jjj

];

SecondTable:

LOAD * INLINE [

C_ID, P_ID, B_ID, Name

1, 101, 50, abd

2, 102, 51, adf

3, 103, 52, fgff

5, 105, 54, egf

6, 106, 55, edffff

15, 115, 64, sdi

16, 116, 65, ljj

17, 117, 66, knj

18, 118, 67, kkji

20, 120, 69, jjj

];

If we do BaseTable minus(-) SecondTable then we will show to below screenshot way.. this is my output.

Regards,

Munna

Re: how to subtract two tables in script level

pokassov — Tue, 02 Jun 2015 08:23:49 GMT

Hello!

See attach.

If we have to do (table1 - table2) and both tables have the same key field then the approach is

1. load second table - table2. moreother, you can loadn only key field

table2:

load id

from ...

2. load with noconcatenate option the first table - table1 with condition not exists (key field)

table1:

noconcatenate

load

from ...

where not exists(C_ID);

3. drop the second table

drop table table2.

It'll work if you have to substract using only key field not data.

Re: how to subtract two tables in script level

vardhancse — Tue, 02 Jun 2015 08:28:58 GMT

Try to use some joins, so that the expected result will come

Re: how to subtract two tables in script level

petter — Tue, 02 Jun 2015 09:03:01 GMT

If the condition is that all columns/fields must have the same values then this could be an approach to eliminate "duplicate" rows:

Table:

LOAD AutoNumber( C_ID & '|' & P_ID & '|' & B_ID & '|' & Name ) AS ExcludeUID INLINE [

C_ID, P_ID, B_ID, Name

1, 101, 50, abd

2, 102, 51, adf

3, 103, 52, fgff

5, 105, 54, egf

6, 106, 55, edffff

15, 115, 64, sdi

16, 116, 65, ljj

17, 117, 66, knj

18, 118, 67, kkji

20, 120, 69, jjj

];

BaseTable:

LOAD * , AutoNumber( C_ID & '|' & P_ID & '|' & B_ID & '|' & Name ) AS UID INLINE [

C_ID, P_ID, B_ID, Name

1, 101, 50, abd

2, 102, 51, adf

3, 103, 52, fgff

4, 104, 53, egf

5, 105, 54, egf

6, 106, 55, edffff

7, 107, 56, deret

8, 108, 57, ffrt

9, 109, 58, gger

10, 110, 59, errg

11, 111, 60, erww

12, 112, 61, c

13, 113, 62, djj

14, 114, 63, jnkl

15, 115, 64, sdi

16, 116, 65, ljj

17, 117, 66, knj

18, 118, 67, kkji

19, 119, 68, mjh

20, 120, 69, jjj

]

WHERE

Not( Exists( 'ExcludeUID', UID ) );

DROP TABLES Table;