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

[#2]

remove the store and drop instructions from your script.

[#3]

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

[#4]

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.

[#5]

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

[#6]

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?

[#7]

Hi,

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

[#8]

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.

[#9]

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;