This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 49%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Hello!
I just ran into a bug in our ADF Pipeline; I am getting the following returned in a Lookup Activity;
I have worked on this for hours. My questions are:
1) what is causing some of the items to have a /r/n after them when there are no spaces in the DB Table
2) I need the logic to say, if the employee does not have a job code in the list below, then I want the process to go to an IF Condition where true means that he gets a Pension and False means he gets nothing. So far my If Condition Activity Expression is:
@hamed1374 (equals(activity('Lookup Job Codes for NO Pension').output , NULL))
but that logic isn't working. Can you please assist? Please see screenshot.
I need to get this back into QA Testing...thanks!! MIke Kiser
@Nasreen Akter @HimanshuSinha-msft @MartinJaffer-MSFT
{
"count": 14,
"value": [
{
"PensionEligable": "C3001"
},
{
"PensionEligable": "CL0099\r\n"
},
{
"PensionEligable": "2711"
},
{
"PensionEligable": "2320C"
},
{
"PensionEligable": "2704\r\n"
},
{
"PensionEligable": "C9504"
},
{
"PensionEligable": "C6067"
},
{
"PensionEligable": "C6068"
},
{
"PensionEligable": "BNT"
},
{
"PensionEligable": "C9500"
},
{
"PensionEligable": "C9502"
},
{
"PensionEligable": "C6094\r\n"
},
{
"PensionEligable": "C9506\r\n"
},
{
Hi @Mike Kiser ,
I think you need a FORLOOP after lookup activity and check if there is any pensionCode present in the activity('Lookup Job Codes for NO Pension').output.value, if yes, set variable to true/false. Hope this helps!
Thanks!
Thanks @Nasreen Akter ! I will implement and try that. Do you think the values that I am getting back from the Lookup with the /r/n after them (shown above) will affect the data? I'm not sure why I am getting this for just a few data items returned.
Thanks!!
Mike Kiser
Hi @Mike Kiser , you can Replace those characters with REPLACE function something like, replace(replace(item().PensionEligable,'\r',''),'\n','')
Thank you @Nasreen Akter !! Where would I put the Replace function; in the Lookup Activity some way?
Thanks again!
Mike
@Mike Kiser , please try to put it inside the FORLOOP-IfCondition e.g., @equal( replace(replace(item().PensionEligable,'\r',''),'\n',''), employee.PensionCode), then if expression is true --> set the variable to true.
So the sequence would be LookUP --> FORLOOP (--> IF, SET VARIBLE)--> IF Variable --> do some action +reset the SET variable if you used this logic inside another forloop.
Thanks!
@Nasreen Akter
@MartinJaffer-MSFT
Hello. I'm back to trying your replace (above)
What I am trying is to get the value of the Lookup in an IF Condition
@replace(replace((activity('Lookup Job Codes for NO Pension').output.value[0].PensionEligable),'\r',''),'\n','')
I'm also trying
@trim(trim((activity('Lookup Job Codes for NO Pension').output[0].PensionEligable),'\r',''),'\n','')
with no luck. It doesn't like the Lookup Activity.....
However I am getting the following error and can't figure out what it wants. Could you please take a look at it? Thanks! Mike Kiser
Hi @Mike Kiser , please try
@trim(activity('Lookup Job Codes for NO Pension').output.value[0].PensionEligable)
or
@replace(replace(activity('Lookup Job Codes for NO Pension').output.value[0].PensionEligable,'\r',''),'\n','')
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
See this example
{
"name": "CleanAndTest",
"properties": {
"activities": [
{
"name": "input",
"description": " { \"PensionEligable\": \"C3001\" }, { \"PensionEligable\": \"CL0099\\r\\n\" }, { \"PensionEligable\": \"2711\" }, { \"PensionEligable\": \"2320C\" }, { \"PensionEligable\": \"2704\\r\\n\" }, { \"PensionEligable\": \"C9504\" }, { \"PensionEligable\": \"C6067\" }, { \"PensionEligable\": \"C6068\" }, { \"PensionEligable\": \"BNT\" }, { \"PensionEligable\": \"C9500\" }, { \"PensionEligable\": \"C9502\" }, { \"PensionEligable\": \"C6094\\r\\n\" }, { \"PensionEligable\": \"C9506\\r\\n\" }",
"type": "SetVariable",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"variableName": "input",
"value": [
{
"PensionEligable": "C3001"
},
{
"PensionEligable": "CL0099\r\n"
},
{
"PensionEligable": "2711"
},
{
"PensionEligable": "2320C"
},
{
"PensionEligable": "2704\r\n"
},
{
"PensionEligable": "C9504"
},
{
"PensionEligable": "C6067"
},
{
"PensionEligable": "C6068"
},
{
"PensionEligable": "BNT"
},
{
"PensionEligable": "C9500"
},
{
"PensionEligable": "C9502"
},
{
"PensionEligable": "C6094\r\n"
},
{
"PensionEligable": "C9506\r\n"
}
]
}
},
{
"name": "Clean values",
"type": "ForEach",
"dependsOn": [
{
"activity": "input",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"items": {
"value": "@variables('input')",
"type": "Expression"
},
"activities": [
{
"name": "Append variable1",
"type": "AppendVariable",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"variableName": "cleaned",
"value": {
"value": "@trim(item().PensionEligable)",
"type": "Expression"
}
}
}
]
}
},
{
"name": "If Condition1",
"type": "IfCondition",
"dependsOn": [
{
"activity": "Clean values",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@contains(variables('cleaned'),pipeline().parameters.testvalue)",
"type": "Expression"
},
"ifFalseActivities": [
{
"name": "Value not found",
"type": "Wait",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"waitTimeInSeconds": 1
}
}
],
"ifTrueActivities": [
{
"name": "Value Found",
"type": "Wait",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"waitTimeInSeconds": 1
}
}
]
}
}
],
"parameters": {
"testvalue": {
"type": "string",
"defaultValue": "2704"
}
},
"variables": {
"input": {
"type": "Array",
"defaultValue": [
{
"PensionEligable": "C3001"
},
{
"PensionEligable": "CL0099\r\n"
},
{
"PensionEligable": "2711"
},
{
"PensionEligable": "2320C"
},
{
"PensionEligable": "2704\r\n"
},
{
"PensionEligable": "C9504"
},
{
"PensionEligable": "C6067"
},
{
"PensionEligable": "C6068"
},
{
"PensionEligable": "BNT"
},
{
"PensionEligable": "C9500"
},
{
"PensionEligable": "C9502"
},
{
"PensionEligable": "C6094\r\n"
},
{
"PensionEligable": "C9506\r\n"
}
]
},
"cleaned": {
"type": "Array"
}
},
"annotations": []
}
}
@MartinJaffer-MSFT : that's great! Cleaner and Simpler approach.
@Mike Kiser : you can try to use TRIM(), CONTAINS() function and also the flow how @MartinJaffer-MSFT appended Cleaned-Code into the Array. Thanks!
Thank you for the compliment :)
Hope it is enough. I do not know why the newlines appear.
Thanks so much both of you! I will be implementing that now; it helps me so much in this rush QA Testing period we are in where now I am fixing the ADF bugs......I'll let you two know how it goes.....
I greatly appreciate you two!!!
Mike Kiser
@Nasreen Akter @MartinJaffer-MSFT
I also have no idea why I am getting the \r\n from the Lookup for just a few of the data items.;.they all are extracted from the same simple DB Table with one column....nvarchar(50)
@Nasreen Akter @MartinJaffer-MSFT
I just realized and wasn't thinking; I am already in a ForEach Loop and when I couldn't find the ForEach present to drag over I realized that you can't have ForEach within a ForEach. With that taken into account, any suggestions on how to get rid of the /r/n from the Lookup?
Thanks again!
Mike Kiser
And @MartinJaffer-MSFT , and I "rebuilt" your example and it is awesome! I have never used an Append Variable and it was a great example on how it is used!
@MartinJaffer-MSFT , I pulled the existing activities out of the existing ForEach Activity so that I can use your idea (your example).....so far it is working great although I haven't finished it yet. I want to say thanks for taking the time to "code" the example in ADF!!
Mike Kiser