topic Re: Calculation based on field values in App Development

Calculation based on field values

Nagaraju_KCS — Sun, 07 Jan 2024 19:13:01 GMT

Hi Community,

I need help on below scenario

If any name is falling under PRIMARY and COVERAGE calculate should be Amount/number of records.

Ex: Canran has two type PRIMARY and COVERAGE, so Amount/2 (for reference added out put column)

Name	Account	Type	Code	Amount	output Column
camberial	E0284	PRIMARY	ABC	100	100/1
cameron	E0210112	PRIMARY	ABC	100	100/1
caren	E1126	PRIMARY	ABC	100	100/2
caren	E11311	COVERAGE	ABC	70	70/2
green	O01104	PRIMARY	ABC	100	100/1
ken	E1513	PRIMARY	ABC	100	100/1
paul	E11021	PRIMARY	ABC	100	100/1
qlik	E1917	PRIMARY	BCA	100	100/1
qs	E021017	PRIMARY	BAC	100	100/3
qs	E0210113	COVERAGE	BAC	35	35/3
qs	E0210145	COVERAGE	BAC	100	100/3
raju	O0139	PRIMARY	ABC	100	100/1
renny	E0132	ROTATION	ABC	100	100/1
sunil	E1234	ROTATION	ABC	100	100/1
sunil	E1716	PRIMARY	ABC	70	100/1

Output should be in straight table and

please note If name falling under PRIMARY and ROTATION should not be consider as 2 (if falling under PRIMARY and COVERAGE we have to consider as 2/3)

This should be set expression, because these fields are not coming from one table.

Code	Amount
ABC	985
BAC	100
BCA	100
Total	1185

@MarcoWedel, @tresesco, @MayilVahanan, @Kushal_Chawda, @marcus_sommer, @stevedark, @sunny_talwar and Experts - can you please take a look

Thanks in Advance!

Re: Calculation based on field values

Nagaraju_KCS — Sat, 06 Jan 2024 12:13:40 GMT

Experts, Any help would be appreciated!

Re: Calculation based on field values

Saravanan_Desingh — Sun, 07 Jan 2024 03:42:19 GMT

I think the below row should be 35/3. If not, please explain

E0210113

COVERAGE

BAC

100/3

Re: Calculation based on field values

Nagaraju_KCS — Sun, 07 Jan 2024 19:12:31 GMT

It was typo that should be 35/3

Re: Calculation based on field values

Saravanan_Desingh — Mon, 08 Jan 2024 01:35:42 GMT

You have to check your sample data again. Try this,

tab1: LOAD * INLINE [ Name, Account, Type, Code, Amount camberial, E0284, PRIMARY, ABC, 100 cameron, E0210112, PRIMARY, ABC, 100 caren, E1126, PRIMARY, ABC, 100 caren, E11311, COVERAGE, ABC, 70 green, O01104, PRIMARY, ABC, 100 ken, E1513, PRIMARY, ABC, 100 paul, E11021, PRIMARY, ABC, 100 qlik, E1917, PRIMARY, BCA, 100 qs, E021017, PRIMARY, BAC, 100 qs, E0210113, COVERAGE, BAC, 35 qs, E0210145, COVERAGE, BAC, 100 raju, O0139, PRIMARY, ABC, 100 renny, E0132, ROTATION, ABC, 100 sunil, E1234, ROTATION, ABC, 100 sunil, E1716, PRIMARY, ABC, 70 ]; Left Join(tab1) LOAD Name, Code, Count(If(Type<>'ROTATION',Type)) As C1 Resident tab1 Group By Name, Code; Left Join(tab1) LOAD Name, Code, Amount/C1 As Output Resident tab1 ;

Re: Calculation based on field values

vinieme12 — Mon, 08 Jan 2024 03:04:13 GMT

As below

= sum(Aggr(sum(Amount)/count(total <Name> Name),Name,Type,Code))

Re: Calculation based on field values

Nagaraju_KCS — Mon, 08 Jan 2024 06:51:07 GMT

Thanks Saravanan, can we write in expression ? because all the fields are not in one table

Re: Calculation based on field values

Nagaraju_KCS — Mon, 08 Jan 2024 07:10:09 GMT

Hi Vinieme12,

if it covers PRIMARY and RORATION under Name - it should not be counted as 2.

(my business rule here if covers PRIMARY and COVERAGE only then have to counted as 2)

Re: Calculation based on field values

Nagaraju_KCS — Fri, 12 Jan 2024 16:11:25 GMT

Still I'm facing issue - experts can you please help

@MarcoWedel, @tresesco, @MayilVahanan, @Kushal_Chawda, @marcus_sommer, @stevedark, @sunny_talwar,

@Anil_Babu_Samineni and experts

Much Appreciated !

Re: Calculation based on field values

Anil_Babu_Samineni — Sat, 13 Jan 2024 14:04:30 GMT

@Nagaraju_KCS Perhaps this?

Sum(Amount)&'/'&If(Match(Type,'PRIMARY','COVERAGE'), Count(Total <Name> Name), Count( Type))