You can try isLong() function in the DataFlow. Please see the screenshot below. Hope this helps! Thanks!
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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:
Hope someone can help. Read about regexmatch, but don't understand much about regex.
Regards
Ron
You can try isLong() function in the DataFlow. Please see the screenshot below. Hope this helps! Thanks!
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