topic Re: how to find dissimilar data? in QlikView

how to find dissimilar data?

swiftfahad — Fri, 31 Aug 2018 04:31:46 GMT

I received two excel files, one is from my warehouse department and one is from my IT.

file 1 say: from Warehouse dept

EmployeeID	Partno	Item	QtyIssue
123	11-22-11-123	Laptop	1
123	11-22-11-124	Table	1
236	11-10-11-111	LED	2
236	11-22-11-125	Mouse	1

file 2 say:from IT dept

EmployeeID	Partno	Item	QtyIssue
123	11-22-11-123	Laptop	1
123	11-22-11-124	Table	1
236	11-10-11-111	LED	2
236	11-22-11-125	Mouse	1
236	11-11-12-202	Projector	2
236	10-21-12-111	Connector	4

Now as u can see IT say the employee ID '236' has so many item that issued to him while ware house sheet said this person has only LED and Mouse.

Now I want to show the table that show the item list which are missing in warehouse dept.

you can suggest any other better representation either by highlighting the missing items or whatever to identify the difference between the two files.

sheet 1-sheet2

remember both are separate excel files.

Re: how to find dissimilar data?

prieper — Fri, 31 Aug 2018 07:40:03 GMT

aircode might be:

WH: LOAD EmployeeID & Partno AS ID FROM Warehouse_DataFile;

Missing:
LOAD EmployeeID & Partno AS ID, * FROM IT_DataFile WHERE NOT EXISTS (ID);

DROP TABLE WH;

HTH

Peter

Re: how to find dissimilar data?

maxgro — Fri, 31 Aug 2018 08:42:17 GMT

You can join the 2 excel files (replace in the script the web load with the excel load)

LOAD EmployeeID,

Partno,

Item,

QtyIssue as QtyW

//'WAREHOUSE' as Dept

FROM

[https://community.qlik.com/thread/312642]

(html, codepage is 1252, embedded labels, table is @1);

join (T)

LOAD EmployeeID,

Partno,

Item,

QtyIssue as QtyIT

//'IT' as Dept

FROM

[https://community.qlik.com/thread/312642]

(html, codepage is 1252, embedded labels, table is @2);

To identify the difference between the two files, you can use an expression in background color

if(sum(QtyIT) <> sum(QtyW), rgb(255,0,0))

Re: how to find dissimilar data?

BalaBhaskar_Qlik — Fri, 31 Aug 2018 08:45:06 GMT

Also check this:

How to compare 2 tables and put non-matching results?

Re: how to find dissimilar data?

swiftfahad — Thu, 06 Sep 2018 04:01:14 GMT

It is not only quantity that missing there is also a row or you can say an item that is not exist in other list.

Re: how to find dissimilar data?

swiftfahad — Thu, 06 Sep 2018 04:12:24 GMT

let me add some brief explanation also :

According to sheet from wharehouse employee 236 has only LED and mouse while IT dept say that this employe also have connector and projector.

I want only those item to display in separate table like projector and connector (i.e fine missing rows)