topic Re: Distinct compare 2 diff tables in load in QlikView

Distinct compare 2 diff tables in load

Thu, 19 Apr 2012 10:50:42 GMT

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 !!

Distinct compare 2 diff tables in load

jagannalla — Thu, 19 Apr 2012 11:27:18 GMT

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.

Re: Distinct compare 2 diff tables in load

Thu, 19 Apr 2012 12:32:53 GMT

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

Re: Distinct compare 2 diff tables in load

Thu, 19 Apr 2012 12:34:47 GMT

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

Re: Distinct compare 2 diff tables in load

jagannalla — Thu, 19 Apr 2012 13:43:17 GMT

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.

Distinct compare 2 diff tables in load

Thu, 19 Apr 2012 13:56:06 GMT

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

Re: Distinct compare 2 diff tables in load

jagannalla — Thu, 19 Apr 2012 14:17:38 GMT

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);

Re: Distinct compare 2 diff tables in load

jagannalla — Thu, 19 Apr 2012 14:35:24 GMT

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

Distinct compare 2 diff tables in load

Thu, 19 Apr 2012 15:02:53 GMT

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