topic Re: Connected Vs Unconnected data in QlikView

Connected Vs Unconnected data

rdsuperlike — Fri, 14 Aug 2015 17:33:15 GMT

hi,

I have Table1(data comes into table 1 from sql database)-->Map Table. This is simplified model for example

I want an exception report that has :

All Table1 Data that made into qlikview MINUS Table1 Data that joined to Map Table.

So, Unconnected - Connected data of table1.

Example:

Table1:

Cust Id	Cust name	Country
1	John Smith	Germany
2	Greg Davis	USA
3	John Ray	UK
4	Davis Ryon	France
5	Megan Josh	USA
6	Bruce Willis	Italy

Map Table:

Order ID	Customer ID	Order Date
10308	2	2/2/2015
10567	4	3/4/2015
18650	5	3/3/2015

So, after loading into qvw we can see that all that r in Italics(greg, davis, davis ryon and Megan Josh) must have joined.

I want to see what all data was not connected. I know here its easy since there r just 3 records not connected. I am talking abt real life scenario where millions of rows that doesnt get connected. I want to figure out a way to get this @records in an exception report of Connected Vs Unconnected data.

Any approach or prototype that someone has already worked on?

Any ideas/suggestions are appreciated.

Thanks

Re: Connected Vs Unconnected data

swuehl — Fri, 14 Aug 2015 17:56:18 GMT

I haven't understood what you are trying to achieve. Could you elaborate with some sample lines of data and your requested result?

Re: Connected Vs Unconnected data

rdsuperlike — Fri, 14 Aug 2015 18:22:00 GMT

Updated the post.

Thanks

Re: Connected Vs Unconnected data

swuehl — Fri, 14 Aug 2015 18:42:28 GMT

You can use NOT EXISTS():

LOAD "Customer ID"

FROM MAPTABLE;

LOAD "Cust ID","Cust Name", Country

FROM Table1

WHERE NOT EXISTS("Customer ID", "Cust ID");

edit:

Attaching a QVW that demonstrates above script based approach in in addition an expression only approach without modifying the original script.