Forum Discussion

MikeFo's avatar
MikeFo
Frequent Visitor
3 years ago
Solved

Group by & Find/Replace?

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 NumberCurrencyCredit
1ABCEUR2,000
1ABCUSD25,000
1ABCHKD50,000
2HIJGBP35,000
3KLMHKD125,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 CurrencyCredit
1ABCEUR2,000
1ABCUSD0
1ABCHKD50,000
2HIJGBP35,000
3KLMHKD125,000

 

Appreciate your thoughts!

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      MikeFo
      Frequent Visitor
      3 years ago

      Thanks, this helped. 

       

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