How to split multiple items separated by ',' into rows and lookup separated values in reference table

[mmvardeleon]

I have below script.  The fields that need to be split are responsible party, product impacted and region impacted. After slitting, we need the values to lookup in the lookup table.  I tried using subfield, and it separated my items, however, I'm unable to look it up in the lookup table.

ErrorLog:
LOAD
ErrorId
,ProcessQueueID
,LinkProcessID as 'LinkProcessIDError'
,[Error Status]
,date([Error Create Date]) as 'Error Create Date'
,date([Error Close Date]) as 'Error Close Date'
,[Caught Before Release]
,ApplyMap('UserMap',LoggedBySID,LoggedBySID) as [Logged By]
,[Caught By Group]
,[Responsibility - Prod]
,ApplyMap('UserMap',QualityVerifier,QualityVerifier) as 'Quality Verifier'
//,[Responsibility - Verifier]

//,[Product Impacted] //multiple selections
,If(Right([Product Impacted],1) = ',',Subfield (Left([Product Impacted],len([Product Impacted])-1),','),Subfield([Product Impacted],',')) as 'Product Impacted'

,[External Team]

//,[Responsible Party] //multiple selections
,If(Right([Responsible Party],1) = ',',Subfield (Left([Responsible Party],len([Responsible Party])-1),','),Subfield([Responsible Party],',')) as 'Responsible Party'

,[Error Description]
,[Corrective Action]
,[Error Type]
,[Root Cause]
,Severity

//,[Region Impacted] //multiple selections
,If(Right([Region Impacted],1) = ',',Subfield (Left([Region Impacted],len([Region Impacted])-1),','),Subfield([Region Impacted],',')) as 'Region Impacted'

,[Over Charge]
,[Under Charge]
,PhoenixId
,AdjustmentAmount
,[Adjustment Required]
,[Reversal Required]
,Approver
,[Approval Date]
,[Approval Status]

Where Exists (ProcessQueueID)
;

SQL
SELECT
Error.ErrorId
,CAST(Error.BmwProductionProcessId as varchar(10))+'-'+CAST(Error.ProcessMonth as varchar(2))+'-'+CAST(Error.ProcessYear as varchar(4)) as ProcessQueueID
,Error.BmwReviewProcessId 'LinkProcessID'
,Status.LookUpDesc 'Error Status'
,Error.CreatedDate 'Error Create Date'
,Error.CloseDate 'Error Close Date'
,CASE WHEN Error.IsCaughtBeforeRelease = 1 then 'Y' else 'N' END 'Caught Before Release'
,Error.CreatedBy as 'LoggedBySID'
,CaughtBy.LookUpDesc 'Caught By Group'
,CASE WHEN Error.IsProductionAnalystResponsible = 1 then 'Y' else 'N' END 'Responsibility - Prod'
,Error.QualityVerifier

//,Error.ProductImpacted 'Product Impacted'
//,CAST(Error.ProductImpacted as int) as 'Product Impacted'
,ProductImpacted.LookUpDesc 'Product Impacted'

,Error.ResponsibleParty 'Responsible Party'
//,CAST(Error.ResponsibleParty as int) as 'Responsible Party'
//,ResponsibleParty.LookUpDesc 'Responsible Party'

,Error.RegionImpacted 'Region Impacted'
//,CAST(Error.RegionImpacted as int) as 'Region Impacted'
//,RegionImpacted.LookUpDesc 'Region Impacted'

,ExternalTeam.LookUpDesc 'External Team'
,Error.ErrorDescription 'Error Description'
,Error.CorrectiveAction 'Corrective Action'
,ErrorType.LookUpDesc 'Error Type'
,RootCause.ErrorRootCauseDesc 'Root Cause'
,Severity.LookUpDesc 'Severity'

,Error.ImpactOverCharge 'Over Charge'
,Error.ImpactUnderCharge 'Under Charge'
,Error.PhoenixId
,Error.AdjustmentAmount
,CASE WHEN Error.IsAdjustmentRequired = 2 then 'Y' else 'N' END 'Adjustment Required'
,CASE WHEN Error.IsReversalRequired = 2 then 'Y' else 'N' END 'Reversal Required'
,Approval.Appv_Checker as 'Approver'
,Approval.Appv_Checker_Dt as 'Approval Date'
,Approval.Overall_Appv_Status as 'Approval Status'
--,Error.BmwProcessTasksIds
--,Error.ModifiedBy
--,Error.ModifiedDate

FROM [BMW].[dbo].[BMW_Error] Error
left join BMW.dbo.bmw_LookUp Status on Error.ErrorStatus = Status.LookUpId
left join BMW.dbo.bmw_LookUp CaughtBy on Error.CaughtBy = CaughtBy.LookUpId
left join BMW.dbo.bmw_LookUp ExternalTeam on Error.ExternalTeam = ExternalTeam.LookUpId

//inner join BMW.dbo.bmw_LookUp ProductImpacted on (If(Right(Error.ProductImpacted,1) = ',',Subfield (Left(Error.ProductImpacted,len(Error.ProductImpacted)-1),','),Subfield(Error.ProductImpacted,','))) = ProductImpacted.LookUpId
inner join BMW.dbo.bmw_LookUp ProductImpacted on [Product Impacted] = ProductImpacted.LookUpId
//left join BMW.dbo.bmw_LookUp ResponsibleParty on Error.ResponsibleParty = ResponsibleParty.LookUpId
//left join BMW.dbo.bmw_LookUp RegionImpacted on Error.RegionImpacted = RegionImpacted.LookUpId

left join BMW.dbo.bmw_LookUp ErrorType on Error.ErrorType = ErrorType.LookUpId
left join BMW.dbo.bmw_ErrorRootCause RootCause on Error.ErrorRootCause = RootCause.ErrorRootCauseId
left join BMW.dbo.bmw_LookUp Severity on Error.Severity = Severity.LookUpId
left join BMW.dbo.BMW_ERROR_APPROVALS Approval on Error.ErrorId = Approval.EventId and Approval.Appv_Maker='BELS_Reversal'
WHERE Error.ProcessMonth is not null
Order by ErrorId
;

[edwin]

is this the part where you are encountering your challenge?
If(Right([Region Impacted],1) = ',',Subfield (Left([Region Impacted],len([Region Impacted])-1),','),Subfield([Region Impacted],','))

i think the if statement will always give you the same value in any test case.  if i understand it corrcetly the expression is doing the following:
if the last character in the field is a comma, get the subfield (first phrase before a comma) else get the subfield (first phrase before a comma)
so whether the lastchar is a comma or not youre actually getting the same subfield so it doesnt make sense to me.

is the content of the field [region impacted]  multiple regions separated by comma?  and you want to split it?  did you just want the first region or all of the regions?

[hugoqliksense]

Maybe like this?

LOAD
SubField([Inventāra Nr.], ';', IterNo()) AS Inv.Numurs,
SubField([Sērijas Nr.],      ';', IterNo()) AS Sēr.Numurs
while (len(SubField([Inventāra Nr.], ';', IterNo())) + len(SubField([Sērijas Nr.], ';', IterNo())))>0;