Data Validation on Key Fields

Tue, 26 Oct 2010 16:10:43 GMT

<body>I have a data table with seven key fields, all of which are different codes. For each field, I must validate that all values within that field exist within a separate "code validation table." I was considering using an INNER JOIN between the data table and each of the "validation tables", but I am not sure this is the best way to do this. Below is an example of my script. I would like to have the “SHIP_TO_CODE” field in the “DataTable” compared against the “SHIP_TO_CODE” field in the “DimCust” table. The outcome of the comparison should be that records containing values in the “SHIP_TO_CODE” field in the “DataTable” that do not appear in the “DimCust” table will be removed. DimCust: LOAD SHIP_TO_CODE, BUSINESS_SEGMENT_CODE, SALES_<st1:stockticker>ORG</st1:stockticker> FROM .. \<st1:stockticker>DATA</st1:stockticker>\QVD\DimCustomer.QVD (qvd) WHERE( BUSINESS_SEGMENT_CODE ='$(vCustBusSegment)' <st1:stockticker>AND</st1:stockticker> SALES_<st1:stockticker>ORG</st1:stockticker> = '$(vSalesOrg)'); DataTable: LOAD FINAL_GI_<st1:stockticker>ACTL</st1:stockticker>_DT_CD, FINAL_GI_<st1:stockticker>ACTL</st1:stockticker>_DT_CD AS <st1:stockticker>DAY</st1:stockticker>_CODE, SHIP_TO_CD, SOH_ORDER_TYPE_CD, SOH_SHIP_COND_CD, SOH_SHIP_PNT_CD, ORDER_MAKEUP_CD, INTER_CO_IN, COUNT_ORDER, SHIP_TO_CODE FROM .. \<st1:stockticker>DATA</st1:stockticker>\QVD\ODSDrvdSAPSalesOrderHdr_ETL.QVD (qvd); Again, my question is whether or not an INNER JOIN is the best way to accomplish this? I have also considered using a MAPPING LOAD, but I am not sure how to apply that functionality in this case. Any assistance is much appreciated. Thanks, Godfrey</body>

Data Validation on Key Fields

Tue, 26 Oct 2010 16:29:33 GMT

Inner Join will compare the two fields and throw out anything that doesn't have a match.

Does your SHIP_TO_CODE field in DataTable contain more than one value? If so, Inner Join will not work, because Inner Join only works on exact matches.

Data Validation on Key Fields

Tue, 26 Oct 2010 17:09:05 GMT

There are many values that exist in the SHIP_TO_CODE field in the DataTable, but each of these values should have a match in the SHIP_TO_CODE field in the DimCust table. When the value for this field exists in DataTable but not in DimCust, I would like the DataTable record to be excluded.

Thanks,

Godfrey

Data Validation on Key Fields

Tue, 26 Oct 2010 17:11:52 GMT

But is there more than one value per field? Like: ShipA, ShipB, ShipC. If so, that doesn't match ShipA.

If there is one value per record in SHIP_TO_CODE in the DataTable, then it seems like Inner Join would work for you.

Data Validation on Key Fields

Wed, 27 Oct 2010 21:58:04 GMT

Take a look at the Keep keyword in the QlikView documentation. AKeep or Left Keep as you load the data will have QlikView do what you need.

Data Validation on Key Fields

Wed, 27 Oct 2010 23:10:55 GMT

Thank you Tim, I had never thought of using an INNER KEEP (because I never knew it existed). I will give that a try.

Thanks,

Godfrey

topic Data Validation on Key Fields in QlikView

Data Validation on Key Fields

Data Validation on Key Fields

Data Validation on Key Fields

Data Validation on Key Fields

Data Validation on Key Fields

Data Validation on Key Fields