question

PoelvanderRERon-6976 avatar image
0 Votes"
PoelvanderRERon-6976 asked PRADEEPCHEEKATLA-MSFT commented

How to check if a parameter in my dataflow is numeric or not?

Hi,

I have a mapping data flow in ADF that takes in a parameter, $Staging_Logical_Key[1].
This parameter can be an IBAN (International Bank Account Number) or a BBAN (Basic bank Account Number).
The IBAN is a string, 18 positions, like 'NL88AAAA3434565677'.
The BBAN is a bigint , length 10, like 3434565677.

The target table contains the alphanumeric IBAN as well as the numeric BBAN.
When the parameter contains the IBAN, I have to compute the BBAN. It is the last 10 positions of IBAN.
When the parameter contains the BBAN, IBAN in target table gets value 'XX'

For field IBAN this is the formula in Derived Column activity:
iif(substring(byName($Staging_Logical_Key[1]),1,2)=='NL' && substring(byName($Staging_Logical_Key[1]),5,4)=='RABO' ,iif(substring(byName($Staging_Logical_Key[1]),9,1)!='0', byName($Staging_Logical_Key[1]),'XX'),'XX')

For field BBAN this is the formula in Derived Column activity:
iif(substring(byName($Staging_Logical_Key[1]),1,2)=='NL' && substring(byName($Staging_Logical_Key[1]),5,4)=='RABO' ,iif(substring(byName($Staging_Logical_Key[1]),9,1)!='0', toLong(right(rtrim(byName($Staging_Logical_Key[1])),10)),toLong(-1)),toLong(-1))

When the parameter $Staging_Logical_Key[1] contains an alphanumeric IBAN both derived columns work.
When the parameter $Staging_Logical_Key[1] contains a numeric BBAN it fails. Doing a substring on a numeric fails.

So, after this long story, the main questions are:
1. How do I check if a field / parameter is numeric ?
2. How do I check if a field / parameter is alphanumeric (containing more than only numbers)?

Hope someone can help. Read about regexmatch, but don't understand much about regex.

Regards
Ron




azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

nasreen-akter avatar image
1 Vote"
nasreen-akter answered

Hi @PoelvanderRERon-6976,

You can try isLong() function in the DataFlow. Please see the screenshot below. Hope this helps! Thanks!

87916-32.jpg



32.jpg (68.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PoelvanderRERon-6976 avatar image
0 Votes"
PoelvanderRERon-6976 answered PRADEEPCHEEKATLA-MSFT commented

Hi

somebody told this would also work

iif(regexMatch(toString(byName($Staging_Logical_Key[1])),'^[0-9]*$'),toLong(byName($Staging_Logical_Key[1]))

just as tip

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PoelvanderRERon-6976,

Glad to know that your issue has resolved. And thanks for sharing the tip, which might be beneficial to other community members reading this thread.

0 Votes 0 ·