topic Re: Aggregate function in num() under script in QlikView

Aggregate function in num() under script

Wed, 03 Feb 2016 19:14:54 GMT

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.

Re: Aggregate function in num() under script

Wed, 03 Feb 2016 19:17:38 GMT

sorry, below is the expression,

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

Re: Aggregate function in num() under script

sunny_talwar — Wed, 03 Feb 2016 19:39:59 GMT

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.

Re: Aggregate function in num() under script

Wed, 03 Feb 2016 20:04:12 GMT

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;

Re: Aggregate function in num() under script

sunny_talwar — Wed, 03 Feb 2016 20:40:59 GMT

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;

Re: Aggregate function in num() under script

Wed, 03 Feb 2016 21:21:55 GMT

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;

Re: Aggregate function in num() under script

sunny_talwar — Wed, 03 Feb 2016 21:31:14 GMT

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;

Re: Aggregate function in num() under script

Wed, 03 Feb 2016 23:29:28 GMT

Thanks much Sunny, now its got executed

Re: Aggregate function in num() under script

sunny_talwar — Thu, 04 Feb 2016 09:26:06 GMT

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

Re: Aggregate function in num() under script

Thu, 04 Feb 2016 14:39:55 GMT

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

Re: Aggregate function in num() under script

sunny_talwar — Thu, 04 Feb 2016 17:58:46 GMT

I think you might have to aggregate each of them in different resident loads as my guess is that you are aggregating them on different levels. If you can share some data with expected output, I might be able to help you resolve your issue.

Re: Aggregate function in num() under script

Fri, 05 Feb 2016 02:35:09 GMT

Thanks sunny, I did aggregation in different resident loads, now I got the result which I'm expecting,thanks much once again for your prompt response which really helped to resolve the same.

Thanks,

Mahesh

Re: Aggregate function in num() under script

sunny_talwar — Fri, 05 Feb 2016 18:00:35 GMT

Awesome, I am glad it finally worked out for you.