topic Compare data from two tables in QlikView

Compare data from two tables

Tue, 25 Aug 2009 00:49:19 GMT

Hi All,

I have two columns getting the data from two different sources ( ftp and oracle database) . I am getting those two columns data in the sheet. I want to compare those two columns data values.How can i do that.

Thanks

AW:Compare data from two tables

biester — Tue, 25 Aug 2009 00:55:43 GMT

Hi,

I'm not quite sure what you are aiming to do, as "Comparing" is a little vague. What do you want to do with the result of the comparison (True or False)? Could you post an example or similar?

Rgds,
Joachim

AW:Compare data from two tables

Tue, 25 Aug 2009 01:11:19 GMT

yes , i want to do true, false and N/A (not available). column a is always correct results.

column a column b compare
1000 1000 true
1001 1010 false
1002 1002 true
1003 1003 true
1004 N/A

thanks for you reply.

Compare data from two tables

Tue, 25 Aug 2009 01:53:21 GMT

it seems like you only need to define a key between both tables.

Compare data from two tables

Tue, 25 Aug 2009 02:12:58 GMT

hello bikkamallas:

what i'll do it's in the script rename both fields with the same name, maybe you can add a field to not lose you're fields, something like this.


tabletmp:
load
 column a,
 column a as _key
from table1;
join
load
 column b,
 column b as _key
from table2;

FinalTable:
noconcatenate
load
 column a ,
 column b,
 if(column a = column b,'true',false) as comparison
 resident tabletmp;
drop table tabletmp;

in this final table you'll have a everything you need.

i hope this help you.

best regards

Enrique

Compare data from two tables

Tue, 25 Aug 2009 02:44:40 GMT

enrique, if you use both columns as keys to join each other, wouldn't your comparison always be true?

Compare data from two tables

Tue, 25 Aug 2009 02:46:31 GMT

thanks for reply. But my data is coming to different sources. one is database table and another one is ftp source.

Directory;
LOAD [Service Location ID]
FROM
sales_and_service_locator_20090319200020.txt
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=pfp_user;Data Source=10US7ASB.xxxxx.COM;Extended Properties=""] (XPassword is xxxxxxxx);
SQL SELECT "CODE" || "LOCATION"
FROM "FP_USER"."PFP_MSTR";

how can i achieve...i am using following statement but not working...

noconcatenate
LOAD
if( [Service Location ID]= "SOLD_TO_CODE" || "DISTRIBUTOR_LOCATION" ,'true','false','N/A') as comparison

Compare data from two tables

Tue, 25 Aug 2009 02:54:36 GMT

did you try creating a common key between both? just name the key with the same name in your script for both loads.

Compare data from two tables

Tue, 25 Aug 2009 04:14:57 GMT

One of the advantange in QlikView is the fact that you can load tables from different source in one single file. All you need to do, is rename the column like dragonauta said to achive that. You can create QVD's file to store each table and after you can renamed them easily.


FtpTable:
LOAD [Service Location ID]
FROM
sales_and_service_locator_20090319200020.txt
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
store * from FtpTable into FtpTable.qvd;
drop table FtpTable;

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=pfp_user;Data Source=10US7ASB.xxxxx.COM;Extended Properties=""] (XPassword is xxxxxxxx);
Oracle:
SQL SELECT "CODE" || "LOCATION"
 FROM "FP_USER"."PFP_MSTR";
store * from Oracle into Oracle.qvd;
drop table Oracle;

then you can load the qvd's files like table files and you can renamed the columns as you please.

After reading dragonauta's post, it make me wonder if you use what i post before you only have to values in the if statement. If exists two equal values in the fields you got TRUE otherwise if in column a or column b exists values that cannot be matched you get FALSE, with this you can get any kind of comparison in the way they two columns are sort, if you need that we have to work another thing.

Best Regards

Enrique

Compare data from two tables

Thu, 27 Aug 2009 02:40:46 GMT

thanks for your reply. It's almost working but i couldn't find the column comparision. table is not showing the records.

Compare data from two tables

Thu, 27 Aug 2009 02:45:37 GMT

remove the store and drop instructions from your script.

Compare data from two tables

Thu, 27 Aug 2009 02:45:50 GMT


Hi All,
load
 column a ,
 column b,
 if(column a = column b,'true',false) as comparison
 resident tabletmp;
drop table tabletmp;

above statement i am looking comparison column. and we are creating tabletmp and droping the table. where the columns is going to be reside.

Thanks

Compare data from two tables

Thu, 27 Aug 2009 03:18:48 GMT

LOAD [Service Location ID],
[Location Type],
[Company Name],
[Location Name],
[Address 1],
[Address 2],
City,
[State/Province],
[Postal Code],
Country
FROM
sales_and_service_locator_20090326200013.txt
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

join
CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=user;Data Source=xxxxx;Extended Properties=""] (XPassword is YRMSLMGB);
SQL SELECT "SOLD_TO_CODE" || '-' || "DISTRIBUTOR_LOCATION",
"DISTRIBUTOR_NAME",
"LOCATION_NAME",
"LOCATION_TYPE"
FROM "USER"."PFP_DISTRIBUTOR_MSTR";

noconcatenate
LOAD
[Service Location ID],"SOLD_TO_CODE" || '-' || "DISTRIBUTOR_LOCATION"
if( [Service Location ID]= ["SOLD_TO_CODE" || '-' || "DISTRIBUTOR_LOCATION"] ,'true','false') as comparison

can you tell me the error on above script.

Compare data from two tables

Tue, 01 Sep 2009 21:24:22 GMT

hi bikkamallas

you can't use a conect string after a join clause but you can switch table position to obtain the same result.


CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=user;Data Source=xxxxx;Extended Properties=""] (XPassword is YRMSLMGB);
SQL SELECT "SOLD_TO_CODE" || '-' || "DISTRIBUTOR_LOCATION",
 "DISTRIBUTOR_NAME",
 "LOCATION_NAME",
 "LOCATION_TYPE"
FROM "USER"."PFP_DISTRIBUTOR_MSTR";
join
LOAD [Service Location ID],
 [Location Type],
 [Company Name],
 [Location Name],
 [Address 1],
 [Address 2],
 City,
 [State/Province],
 [Postal Code],
 Country
 FROM
sales_and_service_locator_20090326200013.txt
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

you need to name the table to allow you to use a resident for the next table.

Best regards

Enrique

Compare data from two tables

Tue, 01 Sep 2009 22:54:21 GMT

thanks a lot for your reply. your idea is working but i have quick question. see below statement..

temptable:
CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=pfp_user;Data Source=xxxxxxxxxx;Extended Properties=""] (XPassword is YRSYPYZMQbXKXZVMSLMGB);
SQL SELECT "SOLD_TO_CODE" || '-' || "DISTRIBUTOR_LOCATION" as DISTLOCATION,
"DISTRIBUTOR_NAME",
"LOCATION_NAME",
"LOCATION_TYPE"
FROM "PFP_USER"."PFP_DISTRIBUTOR_MSTR";
JOIN
LOAD [Service Location ID] as location,
[Location Type],
[Company Name],
[Location Name],
[Address 1],
[Address 2],
City,
[State/Province],
[Postal Code],
Country
FROM
sales_and_service_locator_20090326200013.txt
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

noconcatenate
LOAD
DISTLOCATION,location,
if (DISTLOCATION = location ,'true','false') as comparison
resident temptable;

i can see the comparison column on the select fields list but when i try to do quick table for these 3 columns (row to row) compare , i am unable to do that. could you please help me. how can i compare row to row?

Compare data from two tables

Tue, 01 Sep 2009 23:53:34 GMT

Hi,

if i use the above load statement ,I am getting multiple records for first column. please advice.

Compare data from two tables

Wed, 02 Sep 2009 00:42:55 GMT

i think you don't have any link between the two tables the common fields HAS to have the same name to make the link between them. try to change the names accordingly.

Compare data from two tables

Tue, 02 Aug 2011 20:29:02 GMT

Hello, row by row.

Tab1:

LOAD * INLINE [

A, Key

1000, 1

1001, 2

1002, 3

1003, 4

1004, 5

1005, 6

];

Join

LOAD * INLINE [

B, Key

1000, 1

1010, 2

1020, 3

1003, 4

1040, 5

, 6

];

FinTable:

LOAD A ,

B ,

Key,

if(A=B, 'True', 'False') as Comparison

Resident Tab1;

DROP Table Tab1;