Update a field between 2 tables after load

[meckeard]

I have the following tables loaded in Qlikview:

WealthLosses:

Load

RecNo(),

AccountNumber,

Prev_Day,

0 as Prev_Day_Balance

from Blah.xlsx

Balance:

AccountNumber,

Balance_Date,

Balance

from Blah2.xlsx

What I need to do is update WealthLosses.Prev_Day_Balance with Balance.Balance where the AccountNumber matches and WealthLosses.Prev_Day = Balance.Balance_Date.    It's worth noting that Balance has 3 million rows and a join on the front end times out.  So I'm looking for a way to populate this additional field when it loads.

Thanks!

[asinha1991 · Accepted solution]

There are two ways
1. using map (preferred)

Balance:
Mapping Load
AccountNumber&Balance_Date as key,
Balance
from Blah2.xlsx

WealthLosses:
Load
RecNo(),
AccountNumber,
Prev_Day,
ApplyMap (‘Balance’,AccountNumber&Prev_Day,0) as as Prev_Day_Balance
from Blah.xlsx

//make sure prev_day and balance_date are in same format...if not apply date function directly while passing to applymap

2. using join

WealthLosses_pre:
Load
RecNo(),
AccountNumber,
Prev_Day,
from Blah.xlsx

innerjoin(WealthLosses_pre)

Load
AccountNumber,
Balance_Date as Prev_Day
Balanceas Prev_Day_Balance

from Blah2.xlsx

NoConcatenate
WealthLosses:
Load
RecNo(),
AccountNumber,
Prev_Day,
alt(Prev_Day_Balance,0) as Prev_Day_Balance,

Resident WealthLosses_pre;

Drop table WealthLosses_pre;

[meckeard]

Asinha,

Your first suggestion was very close and only needed a small tweak to work.  Thanks!  I'll keep this in mind should I need it again. 

I have another question but will start a new thread.

Thanks again.