Subracting the Values in 2 Strings in an ADF Expression

Question

Subracting the Values in 2 Strings in an ADF Expression

Mike Kiser 1,536

Hello! This one stumped me. @Nasreen Akter @MartinJaffer-MSFT

I have one String that is say 40
Then I need another String that is 100-40 = 60
The first String which is equal to 40 is stored in the DB Table as an nvarchar(max)

I tried :
@sub (100,variables('VirtualHours'))

but I get: Expression of type: 'Int' does not match the field: 'value'

Any suggestions?

Thanks
Mike Kiser

Anna V 1 Reputation point Microsoft Employee

2021-04-25T21:51:33.593+00:00
Not sure if this will help, but did you have a look at the substring?

Extracts a substring of a certain length from a position. Position is 1 based. If the length is omitted, it is defaulted to end of the string.

substring('Cat in the hat', 5, 2) -> 'in' substring('Cat in the hat', 5, 100) -> 'in the hat' substring('Cat in the hat', 5) -> 'in the hat' substring('Cat in the hat', 100, 100) -> ''

2 answers

Your answer

Anna V 1 Reputation point Microsoft Employee

2021-04-25T21:51:33.593+00:00

Not sure if this will help, but did you have a look at the substring?

Extracts a substring of a certain length from a position. Position is 1 based. If the length is omitted, it is defaulted to end of the string.

substring('Cat in the hat', 5, 2) -> 'in' substring('Cat in the hat', 5, 100) -> 'in the hat' substring('Cat in the hat', 5) -> 'in the hat' substring('Cat in the hat', 100, 100) -> ''

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi @Mike Kiser ,

Thanks for reaching out.

As per your description, I assume your variable variables('VirtualHours') is a string type , if that is the case then please try --> @sub(100, int(variables('VirtualHours'))).
As the function sub expects its parameters to be an integer or a decimal number, you will have to convert the parameter value of type String to int.

In case if you are storing/assigning the output of this expression to a string type variable/parameter then please try this expression --> @string(sub(100, int(variables('VirtualHours')))).

Hope this helps. Do let us know how it goes.

Thanks

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Mike Kiser 1,536 Reputation points

2021-04-26T16:41:44.62+00:00

Hi @KranthiPakala-MSFT
@Nasreen Akter

When I try your expression in my IF Expression:

@greater(string(sub(100, int(variables('VirtualHours')))), '0')

I need to say if VirtualHours is greater than 0 (string) then process

I am getting the following error:

The function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type

Any suggestions? Should the 100 be '100' ?

Thanks!
Mike

Answer 2

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi @Mike Kiser ,

If you are using the expression in your IF Condition activity, please try this --> @greater(sub(100, int(variables('VirtualHours'))), 0)

Do let us know how it goes.

Thank you

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-04-26T18:15:01.857+00:00

Hi @Mike Kiser ,

The expression suggested by @KranthiPakala-MSFT should work if your variable is STRING type. If you still gets the error, would you please tell us what's the type of the variables('VirtualHours')? Thanks!
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-04-26T19:20:07.23+00:00

Hi @Mike Kiser ,

Would you please try with the following expression. Thanks!

@greater(sub(100, float(string(variables('VirtualHours')))), 0)
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-26T20:20:58.827+00:00

Hey @Mike Kiser - If your variables('VirtualHours') contains float values then please use the expression provided by @Nasreen Akter .

Thanks
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-27T17:41:35.887+00:00

Hey @Mike Kiser - Just checking to see if your issue was resolved or still need assistance? - Thanks
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-04-28T17:03:04.213+00:00

Hi @Mike Kiser ,

We still have not heard back from you. Just wanted to check if you are still facing the issue or need assistance on this? If any of the above suggestion answers your query, please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Share via

Subracting the Values in 2 Strings in an ADF Expression

2 answers

Your answer