topic Re: eliminate common records in two different table in QlikView

eliminate common records in two different table

Anonymous — Thu, 10 Oct 2013 09:28:09 GMT

Hi Everyone,

Actually i have a issue to eliminate common record between two table i show u example please help me out.

table a:

invoiceno,amount

1,100

2,100

3,100

table b:

invoiceno,amount

1,100

2,100

3,100

4,200

5,200

i want only display invoiceno-4,5 record others eliminate which are available in table a.

output which i require

table c:

4,200

5,200

Re: eliminate common records in two different table

Gysbert_Wassenaar — Thu, 10 Oct 2013 09:33:42 GMT

Just an example with inline loads. You can replace them with your real load statements.

[table a]:

load * inline [

invoiceno,amount

1,100

2,100

3,100

];

[table b]:

NoConcatenate

load * inline [

invoiceno,amount

1,100

2,100

3,100

4,200

5,200

]

where not exists(invoiceno);

drop table [table a];

If you want to keep table a and b then leave out the last drop table statement and add another load statement to load table b without the not exists statement.

Re: eliminate common records in two different table

v_iyyappan — Thu, 10 Oct 2013 09:49:23 GMT

Hi,

PFA,

Hope its help,

Regards,

Re: eliminate common records in two different table

Anonymous — Thu, 10 Oct 2013 10:36:57 GMT

hi how to store this information in qvd i try to store that remaining record but only invoice id is storein qvd i need to store some other columns also

Re: eliminate common records in two different table

Gysbert_Wassenaar — Thu, 10 Oct 2013 10:45:21 GMT

Use a STORE command to store a table:

Store [table b] into table_c.qvd;

Re: eliminate common records in two different table

saumyashah90 — Thu, 10 Oct 2013 10:49:02 GMT

Store tableb into table_master.qvd(QVD);

then later what you can do is

load * from newtable;

concatenate

load * from table_master.qvd where not exists(invoice id);

Re: eliminate common records in two different table

Anonymous — Thu, 10 Oct 2013 11:54:43 GMT

Hi Guys,

thanks for your reply i try all these solution but data not matching after apply all logic whatever given by you is there any other solution so please let me know.

Re: eliminate common records in two different table

saumyashah90 — Thu, 10 Oct 2013 12:07:48 GMT

what you can do is store everything in qvd for your record.

If you want only new record what you can do is

QVD:

Load * from qvd;

Newdata:

NoConcatenate

load * resident tableb where not exist(invoice id);

this should solve your problem