Aggregate function in num() under script

[#1]

Hi,

I'm facing syntax error while trying to reload after the below expression under Qlikview script,

if(POS_TYPE='Posted',num(sum(NOTIONAL)*-1,'###,###,###.##'),sum( ! !NOTIONAL)) as NETNOTIONAL

Please suggest the correct syntax or way forward for the same rather than implementing in UI and I have to use  in script only.

[#2]

sorry, below is the expression,

if(POS_TYPE='Posted',num(sum(NOTIONAL)*-1,'###,###,###.##'),sum(NOTIONAL)) as NETNOTIONAL

[sunny_talwar]

Are you using a Group By statement in your load? If you have additional fields such as POS_TYPE which are not getting aggregated, you need to use Group By Statement.

[#4]

Yes Sunny, below is the sample code I'm trying for with group by only, but even still throwing expression error

LOAD *,AutoNumberHash128(POS_TYPE,PRIMARYINSTRUMENTIDENTIFIER) as GroupKeyPositionTypeIsssueId

Resident COLLATERALPOSITIONS;

Concatenate

LOAD if(POS_TYPE='Posted',num(sum(NETNOTIONAL)*-1,'###,###,###.##'),sum(NETNOTIONAL)) as NETNOTIONAL,

GroupKeyPositionTypeIsssueId

Resident CollateralPositions

Group by GroupKeyPositionTypeIsssueId;

[sunny_talwar]

You still have not taken POS_TYPE in group by, try adding that to you group by:

LOAD if(POS_TYPE='Posted',num(sum(NETNOTIONAL)*-1,'###,###,###.##'),sum(NETNOTIONAL)) as NETNOTIONAL,

GroupKeyPositionTypeIsssueId

Resident CollateralPositions

Group by GroupKeyPositionTypeIsssueId, POS_TYPE;

[#6]

Thanks sunny, it worked for one condition, but fails at below

LOAD if(VALUATIONPERCENTAGE=0 or VALUATIONPERCENTAGE=100,

        if(POSITIONTYPE='Held',num(sum(MARKETVALUEAGREEMENT),'###,###,###.##'),num(sum(MARKETVALUEAGREEMENT)*-1,'###,###,###.##')),

            if(POSITIONTYPE='Held',num(MARKETVALUEAGREEMENT-(MARKETVALUEAGREEMENT*VALUATIONPERCENTAGE/100),'###,###,###.##'),

                num(sum(MARKETVALUEAGREEMENT)*-1-(MARKETVALUEAGREEMENT*VALUATIONPERCENTAGE/100),'###,###,###.##'))) as AdjPV,

GroupKeyPositionTypeIsssueId

Resident CollateralPositions

Group by GroupKeyPositionTypeIsssueId,POSITIONTYPE;

[sunny_talwar · Accepted solution]

Now you added other non-aggregated fields, so you will need them in Group by as well:

LOAD if(VALUATIONPERCENTAGE=0 or VALUATIONPERCENTAGE=100,

        if(POSITIONTYPE='Held',num(sum(MARKETVALUEAGREEMENT),'###,###,###.##'),num(sum(MARKETVALUEAGREEMENT)*-1,'###,###,###.##')),

            if(POSITIONTYPE='Held',num(MARKETVALUEAGREEMENT-(MARKETVALUEAGREEMENT*VALUATIONPERCENTAGE/100),'###,###,###.##'),

                num(sum(MARKETVALUEAGREEMENT)*-1-(MARKETVALUEAGREEMENT*VALUATIONPERCENTAGE/100),'###,###,###.##'))) as AdjPV,

GroupKeyPositionTypeIsssueId

Resident CollateralPositions

Group by GroupKeyPositionTypeIsssueId,POSITIONTYPE, MARKETVALUEAGREEMENT, VALUATIONPERCENTAGE;

[#8]

Thanks much Sunny, now its got executed

[sunny_talwar]

It is getting executed is a different thing then getting the right output. Are you getting the right number after the execution complete? I have feeling you are not, but if you are then its great.

In case everything is good, do you mind closing this thread down by marking correct and helpful answers.

Best,

Sunny

[#10]

Sunny,

You rightly said I'm not getting the expected result can you shade me any suggestions or sample example to get it corrected.

Thanks,

Mahesh