Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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.