Accessing two lookups

[Former Member]

Hi,

I have a source file and two lookups and based on a condition in source file, i have to access both the lookups and retrieve values. Is it possible to call two lookups simultaneously?

The work flow is like this:

If Source.ColA=0 and lookup1.Col1 = '1', then output value from lookup2.Col2 into target output

If Source.ColA=0 and lookup1.Col1 = '0', then output value from lookup2.Col3 into target output.

I could call only one lookup at a time using lookup_ext function. Is there any other option of doing this?

Appreciate your help.

Thanks,

Arun

[Former Member]

Hi,

Look uop the below link will be useful

Thanks,

Gaurav

[Former Member]

I think that blog has nothing to do with my problem. The blog talks on JDBC adapter and CSV file lookup

[Former Member]

Is it possible to call two lookups simultaneously?

Yes, of course. You can either use lookup in column mapping or use lookup_ext as a function in the Schema Out of Query transform.

I could call only one lookup at a time using lookup_ext function. Is there any other option of doing this?

If the lookup table, compare columns, expression etc. are the same, you can retrieve multiple columns from the lookup table to multiple fields, using one lookup itself if you call it as a function.

Else, you can call two lookups in a Query transform output one each in the mapping tab of two fields.

Regards,

Suneer

[Former Member]

Thanks Suneer.

I did try to call multiple values into a single column in target table using look up table but the lookup is sending default NULL values to the output.

How to prevent NULL values being passed? I do not have a default value or expression to pass. For each output column i am passing a variable that runs a sql query populating data from the database.

For example my lookup Output looks like this

SAP_HAZARD (Expression Unchecked) NULL $Hazard

SAP_NON_HAZARD (Expression Unchecked) NULL $Non_Hazard

SAP_INACTIVE (Expression Unchecked) NULL $Inactive

I am getting first and third outputs into the target column, but for the second output $Non-Hazard, i am getting NULL values.

Appreciate your suggestions?

Thanks,

Arun

[Former Member]

Hi Arun,

I did try to call multiple values into a single column 

You cannot get multiple values (field outputs) to a single column.

Hope you are using lookup_ext.

When using this, you can provide multiple compare columns and expressions.

If you retrieve three output Columns in a single lookup_ext function, they will go to three target columns.

Thats why you have to provide three column names there.

SAP_HAZARD (Expression Unchecked) NULL $Hazard

Are you providing '$Hazard' in column name? Is this a parameter? If this is just a column name, why do you want to prefix a '$'? Could you clarify What are you trying to achieve through this?

Regards,

Suneer

[Former Member]

Hi Suneer,

I have an output for storing material based on categories called Hazardous, Non-Hazardous and Inactive. Each of these categories has separate codes like 200, 300 etc.

The lookup_ext is running on the output column STOR, say the output column name is STOR. I have two lookup tables and a source table. Based on a condition in source table i have to look into other two lookup tables and write codes (200,300 etc representing Hazardous, Non-Hazardous, Inactive) into STOR.

So i have created three global variables called $Hazard, $Non-Hazard and $ Active. These three variables run sql query through a script connected to dataflow and select codes (200,300 etc) for each categories mentioned above.

For example one query : $Hazard = sql('datastore','select 200 from lookuptable 1 where sourcetable.col1=0 and lookuptable1.col2 Like 'S')

so, in the output line (SAP_HAZARD (Expression Unchecked) NULL $Hazard ) should output codes (200, 300 etc) based on the condition in the variable $Hazard.

I think the explanation is very lengthy for you, but to describe the context i have to write atleast this much.

Thanks,

Arun

[Former Member]

Quoting a few pointers which might help though didnt get your req properly.

I have two lookup tables and a source table

One lookup function can lookup into only one table. So, in your case, I believe two lookup functions are required. You would not be passing the output of two lookups into a single target column. You require two target columns. This can be set in the mapping tab of two target columns or called as a single function in Query out. Right click Query out and 'New function call'

From

SAP_HAZARD (Expression Unchecked) NULL $Hazard

if you are using this inside lookup_ext, $Hazard will just be taken as a column name and not a variable.

$Hazard = sql('datastore','select 200 from lookuptable 1 where sourcetable.col1=0 and lookuptable1.col2 Like 'S')

Should'nt this sql query be reconsidered? Syntax would be

sql('DataStore',SELECT TABLE.COLA FROM TABLE WHERE TABLE.COLB = <VALUE> AND TABLE.COLC = <VALUE>);

Are you trying to replace this query with lookup?

Regards,

Suneer

[Former Member]

Suneer,

I think based on your suggestion, i have to rework on this.

"You mentioned that outputs of two lookups are not passed to one target column"

But i have used two lookups in one target column inside an ifthenelse condition.

Like ifthenelse (condition, lookup1, lookup2) and it is working fine.

Should'nt this sql query be reconsidered? Syntax would be

SQL query is correct and i checked that in the SQL server for output.

Are you trying to replace this query with lookup?

I am trying to call the SQL query in the variable called $Hazard and transfer the value from variable to the column.

Thanks,

Arun

[Former Member]

Suneer,

I solved the problem by nested if loops (three ifthenelse) and including three lookups in the then condition.

Thanks,

Arun

[Former Member]

Hi Arun,

Saw your last two posts only now. Good to know it's been resolved. I did'nt know you were using an ifthenelse.

Yes, you can nest multiple lookups in this case.

Regards,

Suneer