AnsweredAssumed Answered

Issue with combining data from CSV and DB to XML

Question asked by rob.parker230111 on Jul 6, 2018
Latest reply on Jul 6, 2018 by rob.parker230111

I'm pulling in a csv file from a disk and mapping it to an xml file. The csv file coming in has a unique record ID for each record, but it's not the system ID that's used to match up records when the xml file is imported. In my process I'm querying a DB connection and pulling in the unique record ID and the system ID, then storing it in a cache. When mapping my csv file to an xml, I'm trying to use a data cache lookup to place the system ID on the xml file, but the xml file is looping incorrect and I'm receiving mixed data records or all unique records have the same system ID (which is the ID of the first record on the csv file).

 

Does anyone have suggestions on how to combine a data element from a DB query with data on a csv file and have them output correctly on an xml? 

 

Input CSV:

RECORDID,BENEFITID,OPTIONID,EFFECTIVESTARTDATE,EFFECTIVEENDDATE

111222333,BenefitCoverage1,HealthCoverage,5/1/2018,5/25/2018

222333444,BenefitCoverage2,HealthCoverage,5/1/2018,5/25/2018

 

Expected XML:

 

<Subscriber SystemID="123" changeType="Update" validFrom="2018-05-01">

      <Coverage>

         <Coverage benefit="BenefitCoverage1" option="HealthCoverage" validFrom="2018-05-26">
            <EffectiveStartDate>2018-05-26</EffectiveStartDate>
            <PayPeriodEmployeeCost>00.00</PayPeriodEmployeeCost>
         </Coverage benefit>
      </Coverage>
</Subscriber>
<Subscriber SystemID="234" changeType="Update" validFrom="2018-06-01">
         <Coverage>
            <Coverage benefit="BenefitCoverage2" option="HealthCoverage" validFrom="2018-06-01">
            <EffectiveStartDate>2018-06-01</EffectiveStartDate>
            <PayPeriodEmployeeCost>09.84</PayPeriodEmployeeCost>
         </Coverage Benefit>
      </Coverage>
</Subscriber>

 

What I'm receiving is one of the following. If using document cache lookup in the map...

<Subscriber SystemID="123" changeType="Update" validFrom="2018-05-01">

      <Coverage>

         <Coverage benefit="BenefitCoverage1" option="HealthCoverage" validFrom="2018-05-26">
            <EffectiveStartDate>2018-05-26</EffectiveStartDate>
            <PayPeriodEmployeeCost>00.00</PayPeriodEmployeeCost>
         </Coverage benefit>
      </Coverage>
</Subscriber>
<Subscriber SystemID="123" changeType="Update" validFrom="2018-06-01">
         <Coverage>
            <Coverage benefit="BenefitCoverage2" option="HealthCoverage" validFrom="2018-06-01">
            <EffectiveStartDate>2018-06-01</EffectiveStartDate>
            <PayPeriodEmployeeCost>09.84</PayPeriodEmployeeCost>
         </Coverage Benefit>
      </Coverage>
</Subscriber>

 

If adding the cache data to the input in the map...

<Subscriber SystemID="123" changeType="Update" validFrom="2018-05-01">

      <Coverage>

         <Coverage benefit="BenefitCoverage1" option="HealthCoverage" validFrom="2018-05-26">
            <EffectiveStartDate>2018-05-26</EffectiveStartDate>
            <PayPeriodEmployeeCost>00.00</PayPeriodEmployeeCost>
         </Coverage benefit>
      </Coverage>
</Subscriber>
<Subscriber SystemID="234" changeType="Update" validFrom="2018-06-01">
         <Coverage>
            <Coverage benefit="BenefitCoverage1" option="HealthCoverage" validFrom="2018-06-01">
            <EffectiveStartDate>2018-06-01</EffectiveStartDate>
            <PayPeriodEmployeeCost>00.00</PayPeriodEmployeeCost>
         </Coverage Benefit>
      </Coverage>
</Subscriber>

<Subscriber SystemID="123" changeType="Update" validFrom="2018-05-01">

      <Coverage>

         <Coverage benefit="BenefitCoverage2" option="HealthCoverage" validFrom="2018-05-26">
            <EffectiveStartDate>2018-05-26</EffectiveStartDate>
            <PayPeriodEmployeeCost>09.84</PayPeriodEmployeeCost>
         </Coverage benefit>
      </Coverage>
</Subscriber>
<Subscriber SystemID="234" changeType="Update" validFrom="2018-06-01">
         <Coverage>
            <Coverage benefit="BenefitCoverage2" option="HealthCoverage" validFrom="2018-06-01">
            <EffectiveStartDate>2018-06-01</EffectiveStartDate>
            <PayPeriodEmployeeCost>09.84</PayPeriodEmployeeCost>
         </Coverage Benefit>
      </Coverage>
</Subscriber>

 

Outcomes