topic Replicated Data analysis - how to apply a filter to a join between two input tables in QlikView

Replicated Data analysis - how to apply a filter to a join between two input tables

Sat, 11 Jul 2009 02:28:40 GMT

I have table A and table B which are identical in structure. They have 100% of the fields in common, so I differentiate them y changing names on all fields except the join field. So far so good. The outcome is a dataset where i can line up fields for comparison between the two tables to check for data accuracy column by column row by row.

I have succeeded in bringing in the full datasets into QV then manually lining up the columns in a chart and testing them there resulting in a 'TRUE' or 'FALSE' outcome in a third result field. This works fine, but I am interested in modifying the load script to perform the evaluations there so that i can return only rows where at least one field has failed validation. In other words, show me just the data problems, not rows where nothing is wrong.

Is there any approach general r specific that one would follow to accomplish this in the load script? I have tried loading the two tables separately with labels, then creating a third input table that would select rows where A.FIELDNAME<>B.FIELDNAME but have been unsuccessful in adding a conditional to the join. It complains that it can't find the one field or the other.

My thought is table A and B are loaded, then a third table created from the conditonal join, then table A and B dropped, leaving just the troubled dataset displayed in the chart.

Any thoughts?

Replicated Data analysis - how to apply a filter to a join between two input tables

prieper — Sat, 11 Jul 2009 03:02:31 GMT

Hi,

think that you are on the right way, please see the attached sample - is this approximately what you need?

HTH

Peter

Replicated Data analysis - how to apply a filter to a join between two input tables

Sat, 11 Jul 2009 07:52:41 GMT

It is defintely headed in the right direction. The problem is it doesn't work when applied to my script... It throws an error:

Field not found - <COMMITMENT_AMT_WF>
Problems:
NOCONCATENATE LOAD
*
RESIDENT
ods
WHERE
Len(COMMITMENT_AMT_ODS) = 0 OR Len(COMMITMENT_AMT_WF) = 0


 ods:
ODBC CONNECT TO <connect string>;
select
deal.deal_cd as "DEAL_CD_ODS" ,
pricing_tranche.pricing_tranche_id AS "PRICING_TRANCHE_ID",
pricing_tranche.commitment_amt AS "COMMITMENT_AMT_ODS"
from
ods_workflow.core_deal deal,
ods_workflow.core_pricing_tranche pricing_tranche
where
pricing_tranche.deal_id = deal.deal_id;
wf:
join
ODBC CONNECT TO <connect string>;
select
deal.deal_cd as "DEAL_CD_WF" ,
pricing_tranche.pricing_tranche_id AS "PRICING_TRANCHE_ID",
pricing_tranche.commitment_amt AS "COMMITMENT_AMT_WF"
from
cfs_workflow.deal deal,
cfs_workflow.pricing_tranche pricing_tranche
where
pricing_tranche.deal_id = deal.deal_id;
Problems:
NOCONCATENATE LOAD
 *
 RESIDENT
 ods
 WHERE
 Len(COMMITMENT_AMT_ODS) = 0 OR Len(COMMITMENT_AMT_WF) = 0;
 DROP TABLE ods;