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
(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
(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.
