Distinct compare 2 diff tables in load

[#1]

Hi Experts

Please help :

I wish to load a table from a Warehouse and a table from Excel.

In the script I wish to compare a created field from the Warehouse and a created field from Excel, see script below :

MatchTab:
LOAD 
'00'&[Parent NSC]&num([Account Number],'00000000')&Value as AccountNumber,
[Approved? N/Y]
/*upper([Customer Name]) as [RCMT Customer Name]*/
FROM

(biff, embedded labels, header is 1 lines, table is RCMT$)
Where [Date received by RCMT] >'06/04/2012';

Join
LOAD 
ACC_NO&text((limit_now)*-1) as WHAccount
FROM $(vFileDate).qvd (qvd)
Where Period_dte >'06/04/2012';

 
FinTable:
     LOAD AccountNumber /*as QVAccount*/ , 
          WHAccount /*as MatchWHAccount*/ ,
          if(AccountNumber=WHAccount and [Approved? N/Y]='Yes', 'Yes', 'No') as Matched
Resident MatchTab;
DROP Table MatchTab;

This should create a field called Matched where if I display as a List box I can tick "Yes" and show all the Matched,

The Errors/Issues I am having are the following :

1) In some instances the AccountNumber and WHAccount would be duplicated , see data below

ACCOUNTNUMBER          WHACCOUNT

123                                   123

444                                   666

555                                   777

123                                   888

456                                   999

Using the Match as it is above, it will match the 123 WHACCOUNT with BOTH the ACCOUNTNUMBERS 123, so if I filter in Matched Yes on table AccountNumber I have a result of 2 , Yet on WHACCOUNT result of 1 . I wish a result of 1 on both !!! ( so only matches First instance!!) I have tried variations of distinct and I cant quite get it

2) If I select "No" on the List box, the data sect remains the same, I would require to filter in all accounts that do not match

3) By using the above method I am creating a table that is the Key between the 2 tables

I need an additional Key of Region, If I rename from Table WHAccount field region_name to Region it will create a key with AccountNumber Table but it will be a syn key as I have already created keys. See viewer below

Guys Thanks a mill

Any thoughts or suggestions on any the above would really be greatly appreciated !!

A

[jagannalla]

I think it is coming based on [Approved? N/Y] field also. You are validating Matched Field with this conditon also. If possible can you attach sample file and show me what you want exactly there.

[#3]

Hi Jagan

I have attached a sample file, I would have expected to have a Match of 2 on BOTH the log and the warehouse.

Actually in this instance the {Approved? N/Y] does seem to be working !!

I would also need to applymap and make region Key ( without creating a syn key) using the data from the Warehouse

Sure let me know what you think ?

Thanks

A

[#4]

Also when I select No in Matched it does not filter the opposite values as in wither the 7 for Warehouse or the 6 for Log

Thanks

[jagannalla]

It is working perfectly according to data only.

We are creating expression if( accountnumber = whaccount and approved ='Y' ). If you see in the image the Warehouse table has only one record for "Yes" matched but, for log table has two records. Thats why it is showing the different count for both fields count. Then what is the issue here.

[#6]

Hi Jagan

I wish for it only match the first instance - I have attached screen shots of the source data.

This is a call off of account so in the Warehouse we may have 1 instance yet on the log we may have it twice, we need to identify where we have duplication on the log and only match the 1st option

Also, it does not filter in the Unmatched when "No" is selected

Thanks

A

[jagannalla]

Ok change your code as below and see.

Warehouse:

LOAD Date,

     Account,

     Customer,

     Account&text((Value)*-1) as WHAccount,

     Value,

     Region_area,

     recno()  as RecWH

FROM

(biff, embedded labels, table is [Warehouse data$]);

Map1:

Mapping

LOAD WHAccount,

    WHAccount as Flag

Resident Warehouse;

LOG:

LOAD [Close Date],

     Sort,

     [Account Number],

     [Customer Name],

     Amount,

     Sort&num([Account Number],'00000000')&Amount as AccountNumber,

     ApplyMap('Map1',Sort&num([Account Number],'00000000')&Amount,Null()) as WHAccount,

     [Approved? N/Y],

     Region,

    recno()  as RecID

FROM

(biff, embedded labels, table is [Log Data$]);

Res_LOG:

LOAD If([Approved? N/Y]='Y', 'Yes', 'No') as Matched,

    WHAccount

Resident LOG

Where Exists(WHAccount);

[jagannalla]

To help you understand i'm attaching sample file. Please check it.

[#9]

Hi Jagan

When I select No on Matched it is filtering in Approved=No,

It should filter in all Unmatched -- identified by Approved<> Yes AND WHAccount <> AccountNumber

Also re matching only first instance of account , in excel what I would do is concatenate the account field with the order number ( using a =COUNTIF(A$2:A2,A2))

What do you think ?

Account	Number
aa	1
bb	1
aa	2
dd	1
ee	1
aa	3
bb	2

Account	Number	ID
aa	1	aa1
bb	1	bb1
aa	2	aa2
dd	1	dd1
ee	1	ee1
aa	3	aa3
bb	2	bb2