I want to create 4 records per object from the payload shown below. Each key has it own mapping which is an integer (ASC__c,CDF23__c,Option_Set__c,Statement_Message_ID__c) which is going to be the ":USRDSB:-FIELD-IND".
**Input JSON **
[
{
"Statement_Message_ID__c": "15673",
"Option_Set__c": "801",
"TSYS_ID__c": "12346849",
"ASC__c": "V01",
"CDF23__c": "1"
},
{
"Statement_Message_ID__c": "15839",
"Option_Set__c": "813",
"TSYS_ID__c": "12347015",
"ASC__c": "V01",
"CDF23__c": "1"
}
]
I have the below dw script. I am iterating over each object in the payload where the size is 2 and I have created an Array fieldDataArray which has a size of 4. So, for each object it has to create 4 records in the flat file. But the script is creating only one per TSYS_ID__c
%dw 2.0
output application/flatfile schemaPath = "schemas/VP.ffd", structureIdent="MultiSegment"
var fieldDataArray= 0 to 3
fun filterKey(keyName, keyArray) = sizeOf(keyArray filter ((item, index) -> item ~= keyName ))==1
---
{
vps: {
vps: [{
":USRDSB:-SOURCE": {
":USRDSB:-SOURCE": "SOURCE",
":USRDSB:-SOURCE-ID ": "MULESOFT"
},
":USRDSB:-HEADER": {
":USRDSB:-HEADER": "HEADER",
":USRDSB:-TRANSMISSION-ID": "VTY"
},
":USRDSB:-DETAIL-RECORD": payload map ( payload01 , indexOfPayload01 ) -> {
( fieldDataArray map {
":USRDSB:-FIELD-IND": flatten(payload01 pluck $$) match {
case k if(filterKey("Option_Set__c",flatten(payload01 pluck $$)) and $$ == 0) -> "019303" as Number
case k if(filterKey("Statement_Message_ID__c",flatten(payload01 pluck $$)) and $$ == 1) -> "014205" as Number
case k if(filterKey("CDF23__c",flatten(payload01 pluck $$)) and $$ == 2) -> "008902" as Number
case k if(filterKey("ASC__c",flatten(payload01 pluck $$)) and $$ == 3) -> "011206" as Number
else -> "1" as Number
}
}),
":USRDSB:-CARD-NBR": payload01.TSYS_ID__c,
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": payload01.Statement_Message_ID__c
}
}]
}
}
FFD:
form: COPYBOOK
structures:
- id: 'MultiSegment'
name: MultiSegment
data:
- groupId: 'vps'
items:
- groupId: 'vps'
count: '>1'
items:
- { idRef: ':USRDSB:-SOURCE', count: 1 }
- { idRef: ':USRDSB:-HEADER', count: 1 }
- { idRef: ':USRDSB:-DETAIL-RECORD', count: '>1' }
segments:
- id: ':USRDSB:-SOURCE'
values:
- { name: ':USRDSB:-COMMENT-IND', type: String, length: 1 }
- { name: 'FILLER1', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-SOURCE', type: String, length: 6, value: 'SOURCE' }
- { name: 'FILLER2', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-SOURCE-ID ', type: String, length: 8, value: 'MULESOFT' }
- id: ':USRDSB:-HEADER'
values:
- { name: ':USRDSB:-COMMENT-IND', type: String, length: 1 }
- { name: 'FILLER1', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-HEADER', type: String, length: 6, value: 'HEADER' }
- { name: 'FILLER2', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-TRANSMISSION-ID', type: String, length: 8, value: 'TBC' }
- { name: 'FILLER3', usage: U, type: String, length: 1 }
- id: ':USRDSB:-DETAIL-RECORD'
values:
- { name: ':USRDSB:-COMMENT-IND', type: String, length: 1 }
- { name: 'FILLER1', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-FIELD-IND', type: Integer, length: 6, format: { justify: ZEROES, sign: UNSIGNED }}
- { name: 'FILLER2', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-CARD-NBR', type: String, length: 19 }
- { name: 'FILLER3', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-CUST-IND', type: String, length: 1 }
- { name: 'FILLER4', usage: U, type: String, length: 1 }
- { name: ':USRDSB:-FIELD-DATA', type: String, length: 80 }
- { name: 'FILLER5', usage: U, type: String, length: 9 }
Current Output:
SOURCE MULESOFT
HEADER VTY
011206 12346849 D 15673
011206 12347015 D 15839
Expected output:
SOURCE MULESOFT
HEADER SPLNSCRB
019303 12346849 352
008902 12346849 02
011206 12346849 D 15673
014205 12346849 F0015
019303 12347015 353
008902 12347015 03
011206 12347015 V03
014205 12347015 F0015
Layout:
SOURCE MULESOFT
HEADER SPLNSCRB
Statement_Message_ID__c TSYS_ID__c
Option_Set__c TSYS_ID__c
ASC__c TSYS_ID__c
CDF23__c TSYS_ID__c
I am missing something on the dw. Could someone help me to understand and fix this?
TIA!
Your description is incomplete but if you want to generate multiple records usually you can either add the records explicitly (eg [ {a:1, b:...}, {a:2, b:...}, {a:3, b:...}, {a:4, b:...} ]) or using more generic code to achieve the same result. After that use flatten() or flatMap() to flatten each subarray into a single output.
In the script you shared the issue is that it is outputting one array for a field, not an array of of the detail records.
It also adds unneeded complexity. Why to convert strings to numbers if they are going to be output as a string? If you want to be sure they are a number then use a number. The way the mapping is done is too complex and doesn't communicate the intention.
I rewrote the script to have a variable that has the mappings. I can map over the entries to get the "number" of records to generate and apply the variable to get the value for the current key. I used output JSON just to illustrate the method. I recommend to use JSON or DW outputs for debugging, then when you have the structure as desired change to a flatfile output. Note how the expression inside flatMap() returns an array of item, which would be the same if we used an explicit array instead.
%dw 2.0
output application/json
var fieldsMapping = {
"Statement_Message_ID__c": "014205",
"Option_Set__c": "019303",
"ASC__c": "011206",
"CDF23__c": "008902"
}
---
{
vps: {
vps: [
{
":USRDSB:-SOURCE": {
":USRDSB:-SOURCE": "SOURCE",
":USRDSB:-SOURCE-ID ": "MULESOFT"
},
":USRDSB:-HEADER": {
":USRDSB:-HEADER": "HEADER",
":USRDSB:-TRANSMISSION-ID": "VTY"
},
":USRDSB:-DETAIL-RECORD": payload flatMap (item) ->
entriesOf(fieldsMapping) map {
":USRDSB:-FIELD-IND": $.value,
":USRDSB:-CARD-NBR": item.TSYS_ID__c,
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": item.Statement_Message_ID__c
}
},
]
}
}
Output:
{
"vps": {
"vps": [
{
":USRDSB:-SOURCE": {
":USRDSB:-SOURCE": "SOURCE",
":USRDSB:-SOURCE-ID ": "MULESOFT"
},
":USRDSB:-HEADER": {
":USRDSB:-HEADER": "HEADER",
":USRDSB:-TRANSMISSION-ID": "VTY"
},
":USRDSB:-DETAIL-RECORD": [
{
":USRDSB:-FIELD-IND": "014205",
":USRDSB:-CARD-NBR": "12346849",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15673"
},
{
":USRDSB:-FIELD-IND": "019303",
":USRDSB:-CARD-NBR": "12346849",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15673"
},
{
":USRDSB:-FIELD-IND": "011206",
":USRDSB:-CARD-NBR": "12346849",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15673"
},
{
":USRDSB:-FIELD-IND": "008902",
":USRDSB:-CARD-NBR": "12346849",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15673"
},
{
":USRDSB:-FIELD-IND": "014205",
":USRDSB:-CARD-NBR": "12347015",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15839"
},
{
":USRDSB:-FIELD-IND": "019303",
":USRDSB:-CARD-NBR": "12347015",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15839"
},
{
":USRDSB:-FIELD-IND": "011206",
":USRDSB:-CARD-NBR": "12347015",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15839"
},
{
":USRDSB:-FIELD-IND": "008902",
":USRDSB:-CARD-NBR": "12347015",
":USRDSB:-CUST-IND": "D",
":USRDSB:-FIELD-DATA": "15839"
}
]
}
]
}
}
This answer is just to explain a method to generate multiple records. You can change the script to add details that you didn't explain in your question.