Compare data from two tables

[#1]

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

[biester]

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

[#3 · Accepted solution]

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

like

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

thanks for you reply.

[#4]

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

[#5]

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

[#6]

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

[#7]

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

like

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

[#8]

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

[#9]

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

[#10]

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