Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
srinivasa1
Creator II
Creator II

2 QVD Compare

Hi

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.

1 Solution

Accepted Solutions
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

change your script like this, than stays only TableD (=TableA without product in both tables) and TableE (=TableB without product in both tables):

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

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

TableD:
NoConcatenate
LOAD * Resident TableA
Where Not Exists(ProductBoth,Product);

TableD:
NoConcatenate
LOAD * Resident TableB
Where Not Exists(ProductBoth,Product);

Drop Table TableA, TableB, TableC;

View solution in original post

23 Replies
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

best way would be an inner join:

Data:
Load * From TableA;

Inner Join (Data)
Load * Fraom TableB;

Miguel_Angel_Baeyens

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.

rbecher
MVP
MVP

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

Astrato.io Head of R&D
srinivasa1
Creator II
Creator II
Author

Hi

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

srinivasa1
Creator II
Creator II
Author

Hi

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.

ToniKautto
Employee
Employee

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

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

srinivasa1
Creator II
Creator II
Author

Hi thanks for your reply.i written code as bellow





A:

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"

,

If

(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.





srinivasa1
Creator II
Creator II
Author

Hi thanks for your reply.i written code as bellow





A:



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"

,

If

(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.