if statement

[SariPari]

I have the below table - 

City	ID	Capacity	HC	MAXCAPACITY	COMBINEDHC	CapacityRank	Output
Bangalore	BLR09	408	318	408	551	1	318
Bangalore	BLR86	265	233			2	233
Boston	BOS04	402	234	402	300	1	300
Boston	BOS50	114	66			2	0

Need the Output as above -

If (MAXCAPACITY > COMBINEDHC, COMBINEDHC ,

 If (MAXCAPACITY < COMBINEDHC, HC))

Also - if MAXCAPACITY > COMBINEDHC only CapacityRank with ranl 1 should have the value and other one should be 0.

Thank you so much.

@swuel @Jagmeet_singh @Sunny @randres_talend @Anil_Babu_Samineni 

[LRuCelver]

If(MAXCAPACITY > COMBINEDHC,
    If(CapacityRank = 1, COMBINEDHC, 0)
    If(MAXCAPACITY < COMBINEDHC, HC, /*MAXCAPACITY = COMBINEDHC goes here*/)
)

[SariPari]

@LRuCelver - Thank you, but that did not work.

[Anil_Babu_Samineni]

@SariPari For City='Bangalore' and ID='BLR86', why there is value in the output?

[mato32188]

Hi @SariPari ,

try to use expression: 

If (MAXCAPACITY > COMBINEDHC, only( {<CapacityRank  = {1}>} COMBINEDHC),

If (MAXCAPACITY < COMBINEDHC, sum(HC),0))

Best regards,

Martin

[Chanty4u]

Try this

if(MAXCAPACITY > COMBINEDHC,

        if(CapacityRank = 1, COMBINEDHC, 0),

        HC) as Output

[SariPari]

In one city, if there are 2 records and Combined HC is < MaxCapacity, then use Combined HC for that city and if combined HC is > Max capacity by City keep original caapcity.

[SariPari]

This 66 should be 0.

[SariPari]

output should have 233 for BLR86

[Anil_Babu_Samineni · Accepted solution]

@SariPari Perhaps this?

If (MAXCAPACITY > COMBINEDHC and CapacityRank=1, COMBINEDHC,
If(Sum(TOTAL <City> HC)>Sum(TOTAL <City> MAXCAPACITY),Sum(HC), 0))