topic Update a field between 2 tables after load in QlikView

Update a field between 2 tables after load

meckeard — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Update a field between 2 tables after load

asinha1991 — Wed, 11 Sep 2019 21:12:38 GMT

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;

Re: Update a field between 2 tables after load

meckeard — Thu, 12 Sep 2019 12:27:13 GMT

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.