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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Calculation

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

My original Table is as follows

Screenshot_1.png

Data Table I want to extract should be as follows

Screenshot_2.png

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

1 Solution

Accepted Solutions
ankur_abhishek
Contributor III
Contributor III

7 Replies
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
upaliwije
Creator II
Creator II
Author

Yes of Course

ankur_abhishek
Contributor III
Contributor III

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)

ss.JPG

upaliwije
Creator II
Creator II
Author

Hi Friends

Pls help me to help a solution for the above

ahaahaaha
Partner - Master
Partner - Master

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

1.jpg

Regards,

Andrey

upaliwije
Creator II
Creator II
Author

Could you pls send me the sample QV doc.

ankur_abhishek
Contributor III
Contributor III

PFA