Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Performing IF statement using fields from two tables

     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!

1 Solution

Accepted Solutions

Re: Performing IF statement using fields from two tables

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;

4 Replies

Re: Performing IF statement using fields from two tables

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

Not applicable

Re: Performing IF statement using fields from two tables

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$);

Re: Performing IF statement using fields from two tables

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;

Not applicable

Re: Performing IF statement using fields from two tables

This works. Thanks Sunny!