Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

anavin33
New Contributor III

import and if-statement help

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.

3 Replies

Re: import and if-statement help

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;

Not applicable

Re: import and if-statement help

Hi

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

Also if possible attach the excel sheets.

Thanks

Sabal

anavin33
New Contributor III

Re: import and if-statement help

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

Community Browser