Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
qv_testing
Specialist II
Specialist II

Calculation based on field values

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!

Labels (1)
9 Replies
qv_testing
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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
;

commqv004.png

vinieme12
Champion III
Champion III

As below

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

 

vinieme12_0-1704682969216.png

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
qv_testing
Specialist II
Specialist II
Author

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

qv_testing
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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))

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