Extracting values from global variable list with multiples "rows"

[eoneon123]

I think this is just a text parsing question as I know how to make this work when extracting a value when there is only a single row of values in a list by way of the following script steps:

 

1. Set var: $MySingleRowListofValues

2. Set var: $List; Value: 1

3. Loop

4.    Exit Loop If [$List  > ValueCount ($MySingleRowListofValues)

5.    Set var: $ValExtract; Value: GetValue ( $MySingleRowListofValues;$List)

6.    ExecuteSQL[insert value from $MySingleRowListofValues]

7.    Set var: [$List; Value: $List + 1]

8.    End Loop

 

What I want to do is create a multi-row list of values via a single SQL step, then extract each value in the looping script to be inserted in step 6. I know how to create step 1, i.e., $MyMultiRowList ofValues via SQL SELECT statement that selects two or more values given a set of WHERE conditions. My problem is extracting them at step 5. I was going to use additional variables ($ValExtract1, $ValExtract2,...) but I don't know how to text parse to extract the values from a multi-row list. I unsuccessfully tried the following:

 

GetValue (MiddleValues ( $MyMultiRowList ofValues ; 1 ; 1 ); $List)

 

Any ideas? For what it's worth, when I plugged this into the data viewer, it returned nothing as opposed to a question mark. 

 

 

 

[dansmith65]

First off, I believe you mean to say multi-column as opposed to multi-row and my answer is based on this assumption.

 

You will need to have a unique column delimiter in each row, so pick something that is unlikely to exist as a value for the fields in question (i'll use | below). You can extract each piece of data in many ways, but since you are familiar with value lists, i'll recommend this method...

Set Variable[$ValExtract; Value: Substitute ( $ValExtract ; "|" ; ¶ )]
Set Variable[$Column1; Value: GetValue( $ValExtract ; 1 )]
Set Variable[$Column2; Value: GetValue( $ValExtract ; 2 )]

Insert the above steps after your current line #5, then use $Column1 and $Column2 in your ExecuteSQL statement.

[eoneon123]

Thanks for the quick response. And yes, I did mean "multi-column". I'll try and plug this is in and see how it goes...

[eoneon123]

It's still not working. Here are the steps I'm taking and I'll indicate where I suspect the problem is occurring:

 

 

1. Set var: $MyList

2. Set var: $List; Value: 1

3. Loop

4.    Exit Loop If [$List  > ValueCount ($MyList)

5.    Set var: $ValExtract; Value: GetValue ( $MyList;$List)

 

//New/modified steps per your suggestions:

 

 6.   Set var: $ValExtract; Value: Substitute ($ValExtract; "|" ; ¶)   //This step doesn't seem to be reflected in the data viewer

 7.   Set var: $Column1; Value: GetValue ($ValExtract; 1)  //Also not reflected

 8.   Set var: $Column2; Value: GetValue ($ValExtract; 2)  //Also not reflected

 

 9.   ExecuteSQL[uPDATE $Column1 and 2 from $MyList]  //Fails to UPDATE

10.  Set var: [$List; Value: $List + 1]

11.  End Loop

 

I'm not sure what I missed from your suggestion? 

[eoneon123]

Got it. In Step 1 where I gathered the values for $MyList, I needed to swap out "|" in place of "" in the initial SQL step. Thanks a million!!!