Group by & Find/Replace?

[MikeFo]

Hi all

I have issues getting the right query for the scenario below. I appreciate it´s rather complicated to understand, but let me give it a try.

Table Name: Register

Credit Number	Currency	Credit
1ABC	EUR	2,000
1ABC	USD	25,000
1ABC	HKD	50,000
2HIJ	GBP	35,000
3KLM	HKD	125,000

Conditions:

If 1 Credit Number has a Currency = EUR && a Currency = USD,

and if Credit for EUR is smaller than Credit for USD,

then I want to keep Credit for EUR and make Credit for USD as 0,

but if Credit for EUR is bigger than Credit for USD, I want to make both Credit as 0

if none of the above, keep Credit as is

Expected Outcome:

Credit Number	Currency	Credit
1ABC	EUR	2,000
1ABC	USD	0
1ABC	HKD	50,000
2HIJ	GBP	35,000
3KLM	HKD	125,000

Appreciate your thoughts!

[amitchandak · Accepted solution]

@MikeFo , Create a new column

new Credit =
Var _Eur = maxx(filter(Table, [Credit Number]=earlier( [Credit Number]) && [Currency]= "EUR"), [Credit])
Var _USD = maxx(filter(Table, [Credit Number]=earlier( [Credit Number]) && [Currency]= "USD"), [Credit])
Switch( True(),
_Eur > _USD && [Currency] in {"EUR", "USD"}, 0,
_Eur < _USD && [Currency] in {"USD"}, 0,
[Credit])

[MikeFo]

Thanks, this helped. 

How would I apply this in the "Transform Data" view i.e. before the Close & Apply step?