Solved: Data Validation on Key Fields - Qlik Community

Report Inappropriate Content · ‎2010-10-26

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

Report Inappropriate Content · ‎2010-10-27

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.

View solution in original post

Report Inappropriate Content · ‎2010-10-26

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.

Report Inappropriate Content · ‎2010-10-26

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

Report Inappropriate Content · ‎2010-10-26

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.

Report Inappropriate Content · ‎2010-10-27

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.

Report Inappropriate Content · ‎2010-10-27

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