Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Catch up on the newest Qlik features and improvements: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
LoKi_asterix
Contributor III
Contributor III

Help with adding lookup values to main table (ApplyMap + Set Analysis returning 0)

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!

 

 

Labels (4)
3 Replies
Chanty4u
MVP
MVP

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)

LoKi_asterix
Contributor III
Contributor III
Author

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

 

QV_Comm.png

 

henrikalmen
Specialist II
Specialist II

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.