Calculation based on field values

[qv_testing]

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!

[qv_testing]

Experts, Any help would be appreciated!

[Saravanan_Desingh]

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

qs

E0210113

COVERAGE

BAC

35

100/3

[qv_testing]

It was typo that should be 35/3

[Saravanan_Desingh]

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
;

[vinieme12]

As below

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

[qv_testing]

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

[qv_testing]

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)

[qv_testing]

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 !

[Anil_Babu_Samineni]

@qv_testing Perhaps this?

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