Calculation

[upaliwije]

I have QV document where data in the following table should be broken

My original Table is as follows

Data Table I want to extract should be as follows

If the CC_CODE = 1 premium coming same BCOD should be split.

EG BW00 Premium 107472934 and Haldumulla Premium should be deducted and shown

Pls help me

[Anil_Babu_Samineni]

Will you able to provide First image data in Excel rather than Image?

[upaliwije]

Yes of Course

[ankur_abhishek]

Hi UPALI

Use this formula for calculation:

if (Aggr(IF(MaxString({<[BCOD]=P([BCOD])>}[BCOD])=[BCOD],[BCOD]),[BCOD])=BCOD,

(num(if (Aggr(IF(MaxString({<[BCOD]=P([BCOD])>}[BCOD])=[BCOD],[BCOD]),[BCOD])=BCOD, PREMIUM),'##,##0')

-

if (Aggr(sum({$<CC_CODE={'CC*'}>}PREMIUM),BCOD)>0,Aggr(sum({$<CC_CODE={'CC*'}>}PREMIUM),BCOD),0)

),PREMIUM)

[upaliwije]

Hi Friends

Pls help me to help a solution for the above

[ahaahaaha]

Hi,

May be like this (look attached file)

Table1:

Directory;

LOAD Distinct ACCOUNTANT,

    BCOD,

    CC_CODE,

    BRANCH,

    PREMIUM

FROM

[TEST (1).xls]

(biff, embedded labels, table is Sheet1$);

NoConcatenate

Table2:

LOAD*,

If(BCOD=Previous(BCOD) And CC_CODE<>1, RangeSum(PREMIUM, peek('PREMIUM_SUM')),

If(BCOD=Previous(BCOD) And CC_CODE=1, PREMIUM*2 - RangeSum(PREMIUM, peek('PREMIUM_SUM')),

PREMIUM)) as PREMIUM_SUM,

If(BCOD=Previous(BCOD) And CC_CODE=1, PREMIUM*2 - RangeSum(PREMIUM, peek('PREMIUM_SUM')),

PREMIUM) as NEW_PREMIUM

Resident Table1

Order By BCOD asc, CC_CODE desc;

DROP Field PREMIUM_SUM;

Left Join(Table1)

LOAD*

Resident Table2;

DROP Table Table2;

Result

Regards,

Andrey

[upaliwije]

Could you pls send me the sample QV doc.

[ankur_abhishek · Accepted solution]

PFA