4 Replies Latest reply: Jan 11, 2017 8:55 AM by Zayd Derweesh RSS

    Performing IF statement using fields from two tables

    Zayd Derweesh

           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!

        • Re: Performing IF statement using fields from two tables
          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

            • Re: Performing IF statement using fields from two tables
              Zayd Derweesh

              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
                  Sunny Talwar

                  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;