7 Replies Latest reply: Feb 12, 2013 5:43 PM by John Lifter RSS

    Lookup Table Usage



      I have noticed that when trying to use a lookup table more than once, I get the following errors;


      - The operator "<name>" has more than one lookup rule that has a non-unique key. Only one lookup rule per operator is allowed to have a non-unique key.


      - The operator "<name>" has more than one iterative rule. Only one iterative rule per operator.


      Can someone expand a little on why this is the case?



      My issue is that I have a lookup table that is basically just a reference list, so I am likely to use a number of variations of data retrievel from it, i.e Lookup column A,B return C. Lookup B,F return A. So the number of Keys changes depending on what I am looking up at the time?




        • Re: Lookup Table Usage

          What you are describing does not sound correct, but it's not possible to confirm without seeing the actual dataflow and table definition.  Are you trying to access the same lookup table in multiple rules in the same transform?


          Could you please provide an export of the project.

            • Re: Lookup Table Usage

              Hi John,


              In answer to your question, Yes, I am trying to access the same Lookup table in a different Lookup Rule, but within the same transform.


              Is that the problem?



                • Re: Lookup Table Usage

                  I haven't tried that scenario, so I'm not certain.


                  Can you redesign to use multiple transform operators?


                  Or, you might be able to do what you want by using a lookup function rule.


                  I would need to know more about what you are trying to do.

                    • Re: Lookup Table Usage

                      Potentially I could use multiple transform operators, however if I have say 10 of these to do it seems a bit messy to have just one transform operator performing one function, I thought that containing similar rules or transforms together was easier for supporting and understanding what has been done.


                      This also links to my other post about best practice design principles for Workflows;




                      The documentation references that a workflow would typically only have 8 or less operators?



                      Perhaps the lookup function rule might help here if that is more flexible,  the help files only reference this as feature for writing back to a lookup table, how can this be used to search on paired values and return another?



                      A further explanation on what I am doing;


                      I have a table with some data, within the data there is an Identifier that is not unique as it can have many rows (financial transactions) so essentially they are a group. The output in the target is a single row for each group with various aggregation along the way.


                      Part of the process is to retreive values from this group based on some inputs, hence the reason for the lookup. The lookup table is essentially a copy of the original so that I can use the Identifier and then (x) to find a specific row, then return another attribute from that row.


                      The value (x) may change depending on what I am looking for, typically this is a derived value within another transform or datascript or one of the original columns.




                        • Re: Lookup Table Usage

                          In addition to the above;


                          Another process I wil be trying achieve usigng the Lookup table is for translations that might change over time.


                          I have a translation table that holds values that need converting to other values,i.e the source will always be the same but I want to use a dynamic translation table as part of the ETL so that if the target requires a different value it can be amended in the translation table rather than updaing some hard code etc..

                        • Re: Lookup Table Usage



                          Have you had any further thoughts on this issue with Lookup tables?


                          I have attempted to use a function rule so that I can programmatically access the Lookup table and use static values for searching. However I am still faced with the same error about only one non-unique lookup rule per transform? Is this something that can be raised as a future enhancement?


                          I see this as disadvantage if I have to create seperate transform operators for each lookup, especially if the best design principles have limits on the number of operators in a workflow.



                            • Re: Lookup Table Usage

                              I think using the same lookup table multiple times within a single transform with different keys is something that would be very hard to support. 


                              If the first query against the table returns say 3 rows and the second query against the table returns 4 rows, how many rows does the operator emit?  How should the operator combine the records returned from the first query with the records returned from the second query.