INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Iterate through Columns per row via Script Component

Iterate through Columns per row via Script Component

(OP)
In one of the packages I am developing I would like to run data through script component and loop through columns whose names contain _CreationID and are not null or blank. I then want to and check that the column values per are the same. However I am struggling to write the vb code in the script editor. I think the process should be

1) loop through columns whose names contain _creationID and are not blank/null
2) load values into an array
3) check if all elements in array are have the same value
4) if the elements have the same value create new output column with value 1
5) if the elements are not matched create new output column with value 0

My code thus far is as follows:

CODE --> vb.net

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

 

        Dim rowType As Type = Row.GetType()

        Dim columnValue As PropertyInfo

        Dim previousRow As String


        For Each columnValue In Row.GetType().GetProperties()
            If (columnValue.Name.EndsWith("_CreationID") And columnValue.GetValue(Row, Nothing).ToString() = "") Then
                ' How can I access they value of the column and load it into and array?
                ' How can I check elements in the array are equal? 
            End If
        Next

        '
    End Sub

End Class 

Maybe my strategy is incorrect and there is an easier way. Any ideas would be appreciated.

RE: Iterate through Columns per row via Script Component

(OP)
TO HELP GIVE A BETTER PICTURE OF THE SITUATION I HAVE ADDED SOME TEST DATA

CODE --> SQL

CREATE TABLE #TEST(
	AUTOMATCHID INT IDENTITY (1,1) NOT NULL
,	A_CREATIONID VARCHAR(20) NULL
,	B_CREATIONID VARCHAR(20) NULL
,	C_CREATIONID VARCHAR(20) NULL
,	D_CREATIONID VARCHAR(20) NULL
,	E_CREATIONID VARCHAR(20) NULL
,	F_CREATIONID VARCHAR(20) NULL
,	G_CREATIONID VARCHAR(20) NULL
,	H_CREATIONID VARCHAR(20) NULL
)


INSERT INTO #TEST
SELECT 'ID12345','ID12345','ID12345','ID12345','ID12345','ID12345','ID12345','ID12345'
UNION ALL
SELECT 'ID12346','ID12349','','','','','',''
UNION ALL
SELECT 'ID12350','','','','','','','ID125454'
UNION ALL
SELECT 'ID12385','','','ID12385','','','',''
UNION ALL
SELECT '','','','','','','',''



SELECT * FROM #TEST 

For every row there are 8 fields (whose name ends with_creationID) which can contain a IDnumber. All 5 _CreationID can be filled or 1 _CreationID can be filled. It is totally varied. If more than 1 _CreationID column is filled a check has to be done on all the values per row to see if they are the same. If they are that row is sent to 1 output and if not it is sent to another output.

In development at the moment we are working with 5 fields but the aim is to have14 fields. Therefore i thought a conditional split expression would be very complicated with so many possible permutations in the fields values.

RE: Iterate through Columns per row via Script Component

Its not clear from your description what you want to do with rows which only have 1 column which isn't null or blank.

Anyway I would just do it as two sql select statements in two different ole db source objects. Or you could union the two together in one select with a hard coded identifier column 'A' & 'B' for the two outputs.

CODE --> SQL

-- Rows with more than one which are all the same
select t.* from test t
inner join
	(select x.AUTOMATCHID, count(distinct creationid) as cid
	from
	(select 
	AUTOMATCHID,A_CREATIONID as creationid
	from test
	union all
	select 
	AUTOMATCHID,B_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,C_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,D_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,E_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,F_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,G_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,H_CREATIONID 
	from test) x
		inner join 
		(select AUTOMATCHID from test 
		where
		(case when A_CREATIONID <> '' then 1 else 0 end +
		case when B_CREATIONID <> '' then 1 else 0 end +
		case when C_CREATIONID <> '' then 1 else 0 end +
		case when D_CREATIONID <> '' then 1 else 0 end +
		case when E_CREATIONID <> '' then 1 else 0 end +
		case when F_CREATIONID <> '' then 1 else 0 end +
		case when G_CREATIONID <> '' then 1 else 0 end +
		case when H_CREATIONID <> '' then 1 else 0 end ) >1) y 
		on x.AUTOMATCHID=y.AUTOMATCHID
		and x.creationid <> ''

  group by x.AUTOMATCHID
  having count(distinct x.creationid) =1) z
on t.AUTOMATCHID=z.AUTOMATCHID

--- Rows with more than one which are not the same
select t.* from test t
inner join
	(select x.AUTOMATCHID, count(distinct creationid) as cid
	from
	(select 
	AUTOMATCHID,A_CREATIONID as creationid
	from test
	union all
	select 
	AUTOMATCHID,B_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,C_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,D_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,E_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,F_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,G_CREATIONID 
	from test
	union all
	select 
	AUTOMATCHID,H_CREATIONID 
	from test) x
		inner join 
		(select AUTOMATCHID from test 
		where
		(case when A_CREATIONID <> '' then 1 else 0 end +
		case when B_CREATIONID <> '' then 1 else 0 end +
		case when C_CREATIONID <> '' then 1 else 0 end +
		case when D_CREATIONID <> '' then 1 else 0 end +
		case when E_CREATIONID <> '' then 1 else 0 end +
		case when F_CREATIONID <> '' then 1 else 0 end +
		case when G_CREATIONID <> '' then 1 else 0 end +
		case when H_CREATIONID <> '' then 1 else 0 end ) >1) y 
		on x.AUTOMATCHID=y.AUTOMATCHID
		and x.creationid <> ''

  group by x.AUTOMATCHID
  having count(distinct x.creationid) >1) z
on t.AUTOMATCHID=z.AUTOMATCHID 

RE: Iterate through Columns per row via Script Component

(OP)
hey I actually managed to get this working with some tweaking. I used a derived column transform to create a field that concatenates the lookup fields with a semi colon delimeter. The expression was as follows:

(ISNULL(F_CreationID) ? "" : F_CreationID) + ";" + (ISNULL(Ind_CreationID) ? "" : Ind_CreationID) + ";" + (ISNULL(ISWC_CreationID) ? "" : ISWC_CreationID) + ";" + (ISNULL(ISRC_CreationID) ? "" : ISRC_CreationID) + ";" + (ISNULL(ISAN_CreationID) ? "" : ISAN_CreationID) + ";" + (ISNULL(EAN_CreationID) ? "" : EAN_CreationID)

I then script transfom. I the loaded the concatenated column into an array. I then deleted elements and checked if the elements were equal and then redirected outputs appropriately. The script was as follows.

CODE --> vb.net

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        '
        ' Add your code here
        ' 
       

        'Call is IsAlphaNumeric function to check if field contains only semi colons. If true then there is no match
        If IsAlphaNumeric(Row.concatCreationID) Then
            Dim vals() As String = Strings.Split(Row.concatCreationID, ";")
            'Creat an split sting by delimeter and load array 
            Dim ListVals As List(Of String) = vals.ToList()
            'Load array contents to list. List is chosen so we can easily add and remore elements
            Dim g As Integer

            'remove non=empty elements from  list
            For g = ListVals.Count - 1 To 0 Step -1
                If ListVals(g) = "" Then
                    ListVals.RemoveAt(g)
                End If
            Next


            'If list contains only 1 item send it directl to Match output
            If ListVals.Count = 1 Then
                Row.MatchCreationID = ListVals(0)
                Row.DirectRowToMatch()
                'If list contains more than 1 element and List check returns true i.e. elements are the same
                'direct row to Match output
            ElseIf ListVals.Count > 1 And Listcheck(ListVals) Then
                Row.MatchCreationID = ListVals(0)
                Row.DirectRowToMatch()
            Else

                Row.DirectRowToSuggestions()


            End If

        Else
            Row.DirectRowToNoMatch()
        End If


    End Sub
    Public Function IsAlphaNumeric(ByVal strToCheck As String) As Boolean
        Dim pattern As Regex = New Regex("[^;*]")

        Return pattern.IsMatch(strToCheck)
    End Function
    Public Function Listcheck(ByVal ListToCheck As List(Of String)) As Boolean

        Listcheck = True

        For I As Integer = 0 To ListToCheck.Count

            If ListToCheck(0) <> ListToCheck(I) Then
                Listcheck = False
                Exit For

            End If

        Next

    End Function 

It seems to have done the trick. Many thanks for your help

RE: Iterate through Columns per row via Script Component

Good stuff, glad you are sorted.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close