Table Index in join recipe in python api

stephanetram

Dear community,

I am trying to modify a join recipe through the python API and I am having an issue with the table index.

I have a join recipe with the following inputs:

{'main': {'items': [{'ref': 'LAYER1_SAPBW_MATERIAL_TEXT_prepared', 'deps': []},
   {'ref': 'LAYER0_SAPBW_MAT_PLANT_ATTR_prepared', 'deps': []},
   {'ref': 'LAYER1_SAPBW_1CL_OMAT001_prepared', 'deps': []},
   {'ref': 'temp_LAYER1_SAPBW_SCL_distinct_stacked', 'deps': []}]}}

and the following joins:

[{'conditionsMode': 'AND',
  'on': [{'column1': {'name': 'Part Id', 'table': 0},
          'column2': {'name': 'Part Id', 'table': 1},
          'dateDiffUnit': 'SECOND',
          'type': 'EQ'}],
  'outerJoinOnTheLeft': True,
  'table1': 0,
  'table2': 1,
  'type': 'LEFT'},
 {'conditionsMode': 'AND',
  'on': [{'column1': {'name': 'Part Id', 'table': 0},
          'column2': {'name': 'Part Id', 'table': 2},
          'dateDiffUnit': 'SECOND',
          'type': 'EQ'}],
  'outerJoinOnTheLeft': True,
  'rightLimit': {},
  'table1': 0,
  'table2': 2,
  'type': 'LEFT'},
 {'conditionsMode': 'AND',
  'on': [{'column1': {'name': 'Part Id', 'table': 0},
          'column2': {'name': 'Part', 'table': 3},
          'dateDiffUnit': 'SECOND',
          'type': 'EQ'}],
  'outerJoinOnTheLeft': True,
  'rightLimit': {},
  'table1': 0,
  'table2': 3,
  'type': 'LEFT'}]

I want to change the column name of the join condition for the table 'LAYER0_SAPBW_MAT_PLANT_ATTR_prepared' but i can't seem to find a correct way to identify the index of the table. I have tried to take the index of the flat_input_refs list or the index given in the virtual inputs, by order of the inputs, but I always end up with the same error:

DataikuException: com.dataiku.dip.exceptions.CodedException: Invalid join condition: Dataset MRPSAP37STOCKMANAGEMENT.LAYER0_SAPBW_MAT_PLANT_ATTR_prepared has no column Part Id

Do you have any idea on how I am supposed to find the index of the table using the python api?

Alexandru

Hi,

It should be "table 0" the order of your input 0,1,2,3 , e.g you need to change all the ->
" 'on': [{'column1': {'name': 'Part Id', 'table': 0}, To correct column name in your dataset.

Hope that helps

Turribeach

While not answering your question directly one good trick when you are creating recipes via the API and want to figure out how things are structured is to create the recipe manually and then go in the DSS DATA_DIR/config/projects/project_ID/recipes/ and inspect the actual recipe.json to understand it's properties better.