topic Re: compare two tables & pick values in QlikView

compare two tables & pick values

Tue, 17 Feb 2015 11:19:12 GMT

hi experts,

i have a table with selective partno, description, date for a particular sales with specific dates.

i have another table with all partnos(not selective), different dates for return of sales.

Now, the return of sales table should compare with the previous sales table & display the partnos of return which are available in

sales table alone. how can i do this.

pls help me out in this regard as soon as possible

Re: compare two tables & pick values

giakoum — Tue, 17 Feb 2015 11:26:14 GMT

you should inner join the tables based on partno

Re: compare two tables & pick values

Tue, 17 Feb 2015 13:00:17 GMT

thanku ioannis giakoumakis.

i hav tried inner join. its not working.

too many records. so system hanging.

what actually i need is, the second table should compare the partnos with first table & second table should display the matching partnos alone.

any suggestions please

Re: compare two tables & pick values

giakoum — Tue, 17 Feb 2015 13:21:06 GMT

what about using the exists function on the second table?

Re: compare two tables & pick values

Tue, 17 Feb 2015 13:40:03 GMT

no. exists function is also not working

Re: compare two tables & pick values

sunny_talwar — Tue, 17 Feb 2015 13:53:39 GMT

Have you tried using Left Keep or Left Join?

If you have some dummy data, it would be easier to do it in a sample qvw file

Best,

Re: compare two tables & pick values

giakoum — Tue, 17 Feb 2015 13:57:41 GMT

join and exists are typical QV functions. They are working if applied correctly. Maybe a sample application would help.

Re: compare two tables & pick values

Anonymous — Tue, 17 Feb 2015 14:02:19 GMT

I believe where exists or look up function should work in your case.

Re: compare two tables & pick values

Tue, 17 Feb 2015 14:15:00 GMT

my tables are

FSSALE:

LOAD Week,

StartDate,

EndDate,

PNO,

DESC

FROM

QVD\FSSALE.qvd

(qvd);

FSSALE_2:

LOAD SALEDATE,

PNO

FROM

QVD\FSSALE_2.qvd

(qvd);

Credit:

LOAD branch as BRCODE,

Date(datelastedited,'DD/MM/YYYY') as CredDate,

pno,

pgcode,

SL_qty,

CR_qty

FROM

QVD\Credit.qvd

(qvd);

FSSALE is my first table. i hav got some loopings for first table to form the table FSSALE_2.

my next table is credit. the pno field from credit table is having records for all sales. it should get matched with PNO in the FSSALE table. & second table(credit) pno should display only the matching values with PNO in

Re: compare two tables & pick values

sunny_talwar — Tue, 17 Feb 2015 14:18:17 GMT

Try this:

FSSALE:

LOAD Week,

StartDate,

EndDate,

PNO,

DESC

FROM

QVD\FSSALE.qvd

(qvd);

FSSALE_2:

LOAD SALEDATE,

PNO

FROM

QVD\FSSALE_2.qvd

(qvd);

Credit:

Left Keep (FSSALE)

LOAD branch as BRCODE,

Date(datelastedited,'DD/MM/YYYY') as CredDate,

pno as PNO,

pgcode,

SL_qty,

CR_qty

FROM

QVD\Credit.qvd

(qvd);

Please note the parts which are bold.

Implement this and see if this works for you.

Best,

Re: compare two tables & pick values

giakoum — Tue, 17 Feb 2015 14:18:24 GMT

add where exists in the Credit table.

and caution, you are naming pno in lowercase in Credit table. pno and PNO are considered 2 different fields in QlikView. they should be both upper or lowercase