topic 2 QVD Compare in QlikView

2 QVD Compare

srinivasa1 — Wed, 27 Apr 2011 11:43:50 GMT

I need to compare 2 file eg

FILE A

Sr no Product

1 P1

2 p2

3 p3

and

FILE AB

Sr no Product

1 P1

2 p4

3 p5

in this i wanted know product exists in both the table pls guide me how i can do this comparison.

thanks in advance.

AW:2 QVD Compare

brenner_martina — Wed, 27 Apr 2011 11:49:57 GMT

Hi,

best way would be an inner join:

Data:
Load * From TableA;

Inner Join (Data)
Load * Fraom TableB;

2 QVD Compare

Miguel_Angel_Baeyens — Wed, 27 Apr 2011 11:49:57 GMT

Hello

Assuming that the key field is "Product", then you can do something like the following:

FileAB:NOCONCATENATE LOAD "Sr no" AS "Sr no AB", Product AS "Product AB", If(Exists(Product), 1, 0) AS ProductExistsInFileAFROM Source;

Hope that helps.

2 QVD Compare

rbecher — Wed, 27 Apr 2011 11:51:46 GMT

Hi,

just load and join these tweo tables:

LOAD [Sr no], [Product] FROM FILE_A.qvd (qvd);
INNER JOIN LOAD [Sr no], [Product] FROM FILE_AB.qvd (qvd);

- Ralf

2 QVD Compare

srinivasa1 — Wed, 27 Apr 2011 12:18:06 GMT

Thanks for your reply.but i need disply that product exists in both the table ..

2 QVD Compare

srinivasa1 — Wed, 27 Apr 2011 12:19:37 GMT

Thanks for your reply.but i need disply that product exists in both the table ..i think inner join will dispy only matching recods in both the table.

SV:Re: 2 QVD Compare

ToniKautto — Wed, 27 Apr 2011 12:40:17 GMT

Add a GROUP BY to to load and you can group the result by Product, and thereby only get one row per product

AW:Re: 2 QVD Compare

brenner_martina — Wed, 27 Apr 2011 12:45:01 GMT

Hi,

but you can load all three tables, TableA, TableB and the inner join of both tables!

AW:Re: 2 QVD Compare

srinivasa1 — Wed, 27 Apr 2011 12:58:08 GMT

Hi thanks for your reply.i written code as bellow

LOAD

Srno

Product

//Product as Key_Product

FROM

QvdCompare.xlsx

(

ooxml, embedded labels

) ;

Join

LOAD

Srno

Product

//Product as Key_Product

FROM

QvdCompare2.xlsx

(

ooxml, embedded labels

);

store

A into

A.qvd;

FileAB:

NOCONCATENATE

LOAD Srno AS "Sr no AB"

Product

AS "Product AB"

(Exists(Product), 1, 0) AS

ProductExistsInFileA

from

A.qvd

(

qvd

);

where i need to use group by if i want show value existed in both the table.

AW:Re: 2 QVD Compare

srinivasa1 — Wed, 27 Apr 2011 12:59:05 GMT

Hi thanks for your reply.i written code as bellow

LOAD

Srno

Product

//Product as Key_Product

FROM

QvdCompare.xlsx

(

ooxml, embedded labels

) ;

Join

LOAD

Srno

Product

//Product as Key_Product

FROM

QvdCompare2.xlsx

(

ooxml, embedded labels

);

store

A into

A.qvd;

FileAB:

NOCONCATENATE

LOAD Srno AS "Sr no AB"

Product

AS "Product AB"

(Exists(Product), 1, 0) AS

ProductExistsInFileA

from

A.qvd

(

qvd

);

where i need to use join if i want show value existed in both the table.

AW:Re: 2 QVD Compare

Wed, 27 Apr 2011 13:27:30 GMT

You want to join 2 files and make flag for show existing products right?

So maybe your if() should be like this:


if(count(product)<=2, 1, 0)

if(understanding<>true, Sorry) ^^

AW:Re: 2 QVD Compare

srinivasa1 — Wed, 27 Apr 2011 13:36:45 GMT

thanks for reply but its not sloved my issue.[:'(]

AW:Re: 2 QVD Compare

Miguel_Angel_Baeyens — Wed, 27 Apr 2011 13:42:12 GMT

Hi,

According to your script now you have a field "ProductExistsInFileA" that stores 1 when the product exists and zero when it doesn't. So if you want to use that in an expression, Sum the number of "1"s in the table

Sum(ProductExistsInFileA)

or just create a listbox for that field and select "1" to see only those that already exist.

Is that what you want?

AW:Re: 2 QVD Compare

rbecher — Wed, 27 Apr 2011 13:46:08 GMT

I don't know what your problem is. If you want to show if product is in two tables you can use the inner join to get a resulting table with all products that are in both tables. You can show this..

The second solution would be a concatenated load with a count aggregation as described above.

If this is not sufficient you should give us more details about your demand.

- Ralf

AW:Re: 2 QVD Compare

Wed, 27 Apr 2011 13:50:01 GMT

so this means i understand it wrong?

previous script was wrong. if(count(product)<1, 1, 0) as flag

or you want load 2 excel file and make table for only existing products? If is it right then just use inner join.

BTW almost always Miguel's right. follow him ^^

AW:2 QVD Compare

brenner_martina — Wed, 27 Apr 2011 14:27:03 GMT

Hi,

look at the attached file!

AW:Re: 2 QVD Compare

srinivasa1 — Thu, 28 Apr 2011 07:41:52 GMT

Hi Thanks for your reply.i have two file as bellow

FILE A

Sr no Product

1 P1

2 p2

3 p3

and

FILE AB

Sr no Product

1 P1

2 p4

3 p5

after compare i need to disply product p1 as its existed in both the table pls help me

AW:Re: 2 QVD Compare

srinivasa1 — Thu, 28 Apr 2011 07:43:45 GMT

Hi Thanks for your reply.i have two file as bellow

FILE A

Sr no Product

1 P1

2 p2

3 p3

and

FILE AB

Sr no Product

1 P1

2 p4

3 p5

after compare i need to disply product p1 as its in both the table pls help me

AW:2 QVD Compare

brenner_martina — Thu, 28 Apr 2011 07:52:45 GMT

Hi,

I have entered a new field in the script: Marker, of course you can also use a flag like 1 As Flag:

TableC:
NoConcatenate
Load "Sr no A" As SRNoAB,
'Product in both tables' As Marker,
Product
Resident TableA;

Inner Join (TableC)
Load "Sr no B" As SRNoAB,
'Product in both tables' As Marker,
Product
Resident TableB;

AW:2 QVD Compare

srinivasa1 — Thu, 28 Apr 2011 08:11:47 GMT

Hi thanks for your reply. im stii little confuse how i can use ur script in my bellow script

TableA:
LOAD
Srno ,
Product

FROM
QvdCompare.xlsx
(ooxml, embedded labels) ;
TableB:
LOAD
Srno ,
Product
FROM
QvdCompare2.xlsx
(ooxml, embedded labels);

can any one pls guide me.