3 Replies Latest reply: Jul 24, 2014 3:39 PM by Ashley Navin RSS

    import and if-statement help

    Ashley Navin

      I'm loading in two different excel files into qlikview and linking them by a field called IDNBR, that is contained in both excel sheets. For some of the accounts there are multiple IDNBRs for each, called by a different name. For each account we want to know where they were created by their branchnbr. The issue I'm having is, if the IDNBR doesnt hold a branchnbr then the field comes up null in my straight table. I'm trying to use an if-statement to check if the additional IDNBRs match the original and get their branchnbr.

       

      This sounds super confusing but here is what I'm currently doing and it doesnt work.

       

      SOR:

      LOAD Contact_Number,

           Contact_Rep_Name,

           Contact_Rep_Code,

           First_Name,

           Last_Name,

           Sort_Name,

           Contact_Name,

           IDNBR,

           Invest_Account,

           Assets_Under_Mgmt,

           Money_Market_Balance,

           Account_Count

      FROM

      [C:\Qlikview\Sor\OFB-7-14-SS.xls]

      (biff, embedded labels);

       

       

      DEPACCTS:

      LEFT JOIN

      (SOR)

      LOAD prod_dt,

           acct_nbr,

           acct_nm,

           nm_line_1,

           branchnbr,

           curr_bal_amt,

           plan_nbr,

           appl_typ_cd,

           id_nbr as IDNBR,

           id_nbr_2,

           id_nbr_3,

           id_nbr_4,

           id_nbr_5

      FROM

      [C:\Qlikview\Sor\dep_accts.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

       

      Tmp:

      Load*,

      if(IsNull(branchnbr),

      if(id_nbr_2 = IDNBR, id_nbr_2,

      if(id_nbr_3 = IDNBR, id_nbr_3,

      if(id_nbr_4 = IDNBR, id_nbr_4,

      if(id_nbr_5 = IDNBR, id_nbr_5,

      IDNBR)))))

      Resident SOR;

      Drop Table SOR;

      Rename Table Tmp to SOR;

       

       

      The issue is with the tmp table. The red part of the if-statements is where I'm stuck. Not sure what to put into that section. I want it to be 'the branchnbr for id_nbr_2' but not sure how to word it.