Performing IF statement using fields from two tables

[#1]

     Hello,

My question is simple. I have the following code:

table1:

LOAD *,

if ( trans_amt > transaction_limit AND transaction_limit > 1, 1, 0) as flag_13_test;

The problem is this: trans_amt is from table1 (previously loaded), while transaction_limit is from table2 (also previously loaded). Both tables linked by a field called CH_ID.

How can I make this work?

Thanks!

[sunny_talwar]

You will either have to join the two tables or use Mapping Load (ApplyMap) or Lookup function to bring the two fields in a single table to perform this if statement. or you can do this on the front end of the application

[#3]

Can you help me understand how this would work in the code? I have tried to do it via a left join. I know the left join works because when I exclude the code in the  original post it works, but when I include it it tells me transaction_limit not found. Here is the relevant code for reference:

table1:

LOAD *,

if ( trans_amt > transaction_limit AND transaction_limit > 1, 1, 0) as flag_13_test;

table1:

LOAD

    pkey,

    trans_ID,

    debit_amt,

    credit_amt,

    ch_id,

    trans_date,

// The next set of fields are calculated, and will be included in final_flag_detail table:

    WeekDay (trans_date) as trans_weekday,

    Day(trans_date) as trans_day,

    Month(trans_date) as trans_month,

    Year(trans_date) as trans_year,

    debit_amt + credit_amt as trans_amt,

FROM [lib://table1.xlsx]

(ooxml, embedded labels, table is [Transaction Detail]);

table2:

LEFT JOIN LOAD

    appr_code_name,

    ch_ln_fn,

//     area_office,

//     ch_acc_no,

//     ch_last_name,

//     ch_first_name,

    ch_id,

    transaction_limit

FROM [lib://data/table2.xls]

(biff, embedded labels, table is export_sas$);

[sunny_talwar · Accepted solution]

Something like this:

table1:

LOAD

    pkey,

    trans_ID,

    debit_amt,

    credit_amt,

    ch_id,

    trans_date,

// The next set of fields are calculated, and will be included in final_flag_detail table:

    WeekDay (trans_date) as trans_weekday,

    Day(trans_date) as trans_day,

    Month(trans_date) as trans_month,

    Year(trans_date) as trans_year,

    debit_amt + credit_amt as trans_amt 

FROM [lib://table1.xlsx]

(ooxml, embedded labels, table is [Transaction Detail]);

Left Join (table1)

LOAD appr_code_name,

    ch_ln_fn,

//    area_office,

//    ch_acc_no,

//    ch_last_name,

//    ch_first_name,

    ch_id,

    transaction_limit

FROM [lib://data/table2.xls]

(biff, embedded labels, table is export_sas$);

FinalTable:

LOAD *,

    if(trans_amt > transaction_limit AND transaction_limit > 1, 1, 0) as flag_13_test

Resident table1;

DROP Table table1;

[#5]

This works. Thanks Sunny!