if condition fields from 2 tables

[#1]

Hi, I have an if condition that contains fields from 2 different tables so I tried to concatenate the tables and when run, I received error message saying the field is not found. This is my current script:

INVENT:

LOAD *, IF(INVENTLOCATION='BTSTORE',REORDERPOINT) AS 'ROP(BT)';

SQL SELECT

.

.

.(OTHER FIELDS)

.

ORDERQTY

INVENTLOCATION

.

.

FROM <FILENAME>;

CONCATENATE(INVENT)

SQL SELECT

.

.

.

REORDERPOINT

.

.

FROM <FILENAME>;

Basically I want to create a column that reads from 2 tables.

Thanks!

[eduardo_sommer]

Hi, Rachel

This is not the meaning of the concatenate. To reference a field in the load command, it must exist in a precedeing load (in your case, the SQL SELECT) or it must exist in the file you are loading.

In your case, you can make a join of the two source tables, before the load or you load both tables, join them in load/join commands end only then you make the load of the INVENT table referencing the fields you need.

Eduardo

[Anonymous]

Try something like this:

Tab1:

SQL SELECT

.

.

.(OTHER FIELDS)

.

ORDERQTY

INVENTLOCATION

.

.

FROM <FILENAME>;

concatenate

Load

fieldnames;

SQL SELECT

.

.

.

REORDERPOINT

.

.

FROM <FILENAME>;

noconcatenate

Final:

Load *, IF(INVENTLOCATION='BTSTORE',REORDERPOINT) AS 'ROP(BT)';

resident Tab1;

[#4]

Hi Rachel,

You can do something like in your script

Table1:

SQL SELECT *

From TB1;

Join

SQL SELECT *

FROM TB2;

New_Table:

Load * , (IF(INVENTLOCATION='BTSTORE',RECORDPOINT ) AS 'POP(BT)'

RESIDENT Table1;

drop table Table1;

Hope this will help you.

-- Rajendra

[eduardo_sommer]

Hi Nitin,

It seems to me that you'll never have the fields INVENTLOCATION and REORDERPOINT on the same row. If this is true, the field [ROP(BT)] will always have a null content.

For this reason, I proposed a join between the two source tables.

Eduardo

[eduardo_sommer]

I think this will work, as soon as you have joining fields in bothe tables (TB1 and TB2).

Eduardo

[#7]

Yes to solve this type problems we must join the table but need to remember join should be used appropriate according to data.