Lookup issue

[davehutchinson]

Hi guys, this will probably be an easy one but i'm stumped.

I have 2 tables loaded.  The first is: "NGD_Test_Data" which has a list of tests with an "Outcome Code" by date.

The second is "NGD_Outcome_Codes_Table" which has a list of all possible Outcome Codes ("OutcomeCode") and then an explanation of what they mean ("ProblemExplanation").

I want to add another column into the 2nd table, with the count of tests.

Resident [NGD_Test_Data] Group By [NGD Code] ;

Should be easy right?  but I've tried everything and nothing is working. 

I created a new table with the NGD code and then looked up the explanation, but this didn't work.  i've tried swapping the field name order too and that hasn't worked.

I read that concatenate might be the way to go, but that didn't work either....

and I've also tried doing a count in the NGD_Outcome_Codes_Table of number of tests, but it didn't work, as presumably they're not joined.

This should be quite a straightforward thing to do IMO, but I've search and searched and haven't found the answer.

Can anyone help?

Thanks

[Ryan_McGregor]

Hello there, 

As I understand the issue, I would approach it like this:

OutcomeCodesTable:

Load Distinct

OutcomeCode

Resident NGD_Outcome_Codes_Table;

Inner Join Load

OutcomeCode

count(Outcome Date or UniqueID) as TotalTests // this works best if you have some unique line identifier, you can either make one using just RowNo() or make a custom one like ('OC'&RowNo()).

Resident NGD_Test_Data;

This should provide a third table that will associate to your OutcomeCode on both tables.

[davehutchinson · Accepted solution]

So I managed to sort this by a slightly different method.

Turns out I needed to have my outcome codes table before the data in the data load editor list.

Then I added an "applymap" line into the test data to lookup the explanation:

applymap('NGD_Outcome_Codes_Table_map',vw_ngd_consumer_ee_pn_trdb.dtr_code,'No Explanation Found') as ProblemExplanation,

This has done the trick