Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm trying to adjust an amount in my main table by adding a value from a lookup file whenever a few key fields match. I've hit a wall though my Set Analysis expression keeps returning 0, and a normal Sum gives me a completely unexpected number.
Here is what I’m working with:
Here is what I’m working with:
My Tables:
• Lookup file: Date, Account Number, Contract Reference, AmountToAdd
• Main table: Date, Account Number, Contract Reference, AmountOriginal, AccountType (and a bunch of other fields)
What I need to happen:
I want to create a new field called AmountAdjusted. When Date, Account Number, and Contract Reference match between the two tables, it should add AmountOriginal + AmountToAdd. If there's no match, it should just keep AmountOriginal.
What I've tried in the script:
ApplyMap(
'MapAmountadjusted',
Trim([Contract Reference]) & '|' & Trim([Account Number]) & '|' & Num(Floor(Date)),
AmountOriginal
) AS AmountAdjusted
Where I'm stuck:
1. When I use this expression on the front end, I just get 0:qlik
Sum({$<Year={'$(vMaxYear)'}, %Date={'$(vMaxDate)'}, AccountType={'001','002'}>} AmountAdjusted)
2. If I just use Sum(AmountAdjusted) and manually filter the Year and Date in the app, I get a number, but it doesn't make sense compared to what I expect.
Am I going about the ApplyMap addition the wrong way, or is something off with my Set Analysis? Any pointers would be hugely appreciated!
Hi.try this
MapAmount:
Mapping LOAD
Trim([Contract Reference]) & '|' &
Trim([Account Number]) & '|' &
Num(Floor(Date)) as Key,
AmountToAdd
FROM LookupFile;
Main:
LOAD *,
AmountOriginal +
ApplyMap(
'MapAmount',
Trim([Contract Reference]) & '|' &
Trim([Account Number]) & '|' &
Num(Floor(Date)),
0
) AS AmountAdjusted
FROM MainFile;
In front end
Sum({
$<
Year={'$(vMaxYear)'},
%Date={'$(vMaxDate)'},
AccountType={'001','002'}
>
} AmountAdjusted)
Just to clarify what I’m trying to achieve, I’ve already implemented that approach, but the results aren’t coming out as expected.
Ideally, AmountAdjusted shouldnt match AmountOriginal. While AmountToAdd is being populated correctly, there seems to be an unexpected zero value somewhere in the calculation. I suspect that’s what’s causing the discrepancy and preventing the final result from matching as expected.
I’ve included a snippet below to better illustrate the issue
You say that if you manually filter the Year and Date in the app, it works. That makes me think that your issue is with the set expression rather than with the script. I would start looking at year and date, perhaps e.g. $(vMaxDate) is not in the same format as %Date and therefore the filtering fails.