I'm receiving the following csv
0000595182;3
0000504290;
0000710842;3
0000754608;1
0000489193;6
0000793814;4
0000580308;5
0000399045;
0000068910;
0000210986;2
0000908352;5
0000130721;
0000097876;2
0000185893;
0000218924;
0000456669;4
0000671520;4
0000796097;1
0000709024;3
0000203990;
0000205763;8
0000218211;4
0000409543;
0000994506;5
I'm trying to transcode the one digit number on the right (2nd Column) to the CodeOT on the following json table.
[
{
"CodeSap": null,
"CodeOT": 1
},
{
"CodeSap": 0,
"CodeOT": 1
},
{
"CodeSap": 1,
"CodeOT": 2
},
{
"CodeSap": 2,
"CodeOT": 2
},
{
"CodeSap": 3,
"CodeOT": 2
},
{
"CodeSap": 4,
"CodeOT": 2
},
{
"CodeSap": 5,
"CodeOT": 2
},
{
"CodeSap": 6,
"CodeOT": 3
},
{
"CodeSap": 7,
"CodeOT": 3
},
{
"CodeSap": 8,
"CodeOT": 3
},
{
"CodeSap": 9,
"CodeOT": 3
}
]
So I need to match the digit on the right of the csv to the CodeSAP and then change it to the CodeOT that matches to get this output:
{
"listeMaj": [
{
"refSig": "0000595182",
"risqueFinancier": "2"
},
{
"refSig": "0000710842",
"risqueFinancier": "2"
}
]
}
To get there I tried this but i seem to be getting an unusual error as if I can't pass dynamic arguments. Here's my code :
%dw 2.0
input payload application/csv header=false,separator=";"
output application/json
var pldFix= payload map ((value,key)->
{refSig: value.column_0,
indicateur: value.column_1}
)
fun Find(val) = (trTable.CodeSap find (if(val == "" or val == null) null else val as Number))
var test = pldFix map ((value, key) ->
if (value.refSig == null or sizeOf(value.refSig) == 10)
okLines :{
refSig: value.refSig,
risqueFinancierBF: (trTable[Find(value.indicateur)[0]].CodeOT)
}
else
{
koLines: {
refSig: value.refSig,
risqueFinancierBF: if(value.indicateur == "") null else value.indicateur as Number
}
}
)
var koLines = flatten([test.*koLines])
var okLines = flatten([test.*okLines])
---
okLines
And the error is about dynamic arguments passed to the function, which I don't understand why?
You called the function 'Dynamic Selector' with these arguments:
1: Array ([{CodeSap: null,Libelle: "",CodeOT: 1}, {CodeSap: 0,Libelle: "Elle a demandé ...)
2: Null (null)
But it expects one of these combinations:
(Array, Range)
(Array, Number)
(Array, Name)
(Array, String)
(Binary, Range)
(Binary, Number)
(Date, Name)
(DateTime, Name)
(LocalDateTime, Name)
(LocalTime, Name)
(Object, Name)
(Object, Number)
(Object, String)
(Period, Name)
(String, Range)
(String, Number)
(Time, Name)
14| risqueFinancierBF: (trTable[Find(value.indicateur)[0]].CodeOT)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Trace:
at main::main (line: 14, column: 41)
The constraint is I also have to get the data from a database so it might change over time. And I have to check for the refSig to be at 10 characters exactly. I didn't get successfull as I keep getting "Cannot coerce Null(null) to Number" errors too. Any advice or help ?
The code that you provided is working with the inputs that you have shared. Probably you are getting a indicateur that does not exists as any CodeSap and due to which your Find function is returning a blank array [] and therefore your expression trTable[Find(value.indicateur)[0]] results as trTable[null] due to which you are getting the error.
As an additional suggestion you can try to use join and avoid writing all the extra functions. Join will join the two arrays based on the parameter that you will provide and give a joined array as output that you can easily map as per your output.
For details on the output structure you can refer to the official doc. The output can be confusing initially but it works the same way as join works in SQL. You can use leftJoin or outerJoin if you want to including the records that does not have corresponding CodeSap
%dw 2.0
import join from dw::core::Arrays
input payload application/csv header=false,separator=";"
output application/json
---
join(
payload filter (sizeOf($.column_0) == 10),
vars.trTable,
(record) -> record.column_1,
(code) -> code.CodeSap default "" // in csv there are no null, so null needs to be treated as blank string for matching with corresponding csv record
)
map {
refSig: $.l.column_0,
risqueFinancierBF: $.r.CodeOT
}