import and if-statement help

Anonymous — Thu, 24 Jul 2014 19:19:23 GMT

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.

Re: import and if-statement help

Anonymous — Thu, 24 Jul 2014 19:24:32 GMT

Only had a quick glance, but try the pink bold below.

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))))) as IDNBR

Resident SOR;

Drop Table SOR;

Rename Table Tmp to SOR;

Re: import and if-statement help

Thu, 24 Jul 2014 19:25:34 GMT

Can you please attach your QVW so that it is more clear?

Also if possible attach the excel sheets.

Thanks

Sabal

Re: import and if-statement help

Anonymous — Thu, 24 Jul 2014 19:39:50 GMT

Okay so I did that but nothing changed. I think its because now all the correct id numbers are saved but there is still no link between the branchnbr and idnbr

topic Re: import and if-statement help in QlikView

import and if-statement help

Re: import and if-statement help

Re: import and if-statement help

Re: import and if-statement help