Creating Custom Function To Write Contents Of Table Field To Variable

[simeonlukich]

Hello All,

First post, so am sorry if i am posting this in the wrong place or or am missing something.

I am trying to write a function/expression so that i can input a table name and a field and it returns all of the field elements separated by commas. I couldn't find an easier way to do this so custom code it is.

Here is what i have so far

SET tableFieldToString =

  LET vReturn_TFTS = ''chr(59)

  LET vNumRows = NoOfRows($1)chr(59)

  IF $(vNumRows) > 0 THEN

  FOR i = 1 to $(vNumRows)

  LET vReturn_TFTS = '$(vReturn_TFTS)' & Peek($2,$(i),$1)chr(59)

  IF i < $(vNumRows) THEN

  LET vReturn_TFTS = '$(vReturn_TFTS)' & ','chr(59)

  ENDIF

  NEXT

  ELSE

  $(vReturn_TFTS)chr(59)

  ENDIF

;

The idea would be to call the above function in the following way

$(tableFieldToString('tableName','fieldName'));

To explain the logic.

- set the return variable to blank

- count the number of rows in the table

- if the number of rows in the table is greater than zero then

    - Iterate through each row of the table

    - Peek at the field given the row number and table then amend it to the return variable

    - Make sure to add a comma between records

- Else return a blank variable

Ultimately i am trying to use the comma separated list in a match statement during a load so i can exclude values in one table from another. The data/script would look something like this.

RemoveTheseValues:

LOAD * INLINE [

_Key

1

2

3

4

5

.

..

...

];

NewTableWithRemovedValues:

LOAD *

RESIDENT SomeTable

WHERE NOT match(_Key,$(tableFieldToString('RemoveTheseValues','_Key')));

Any help or alternate suggestions would be greatly appreciated.

Thanks

Simeon

[marcus_sommer · Accepted solution]

You didn't need this you could simply use exists() for this, see as an example: The exists issue. And if you need to get (all) field-values you could use field-functions like fieldvalue() which meant you looped through the distinct values from the field directly and it's much faster then the table-loop per peek().

- Marcus

[sasiparupudi1]

Hi

As Marcus says, you can make use of the exists function to filter the records but if you ever want to construct a string of delimeted field values, you may use the following method also

xx:

LOAD Concat(_Key,',') as _Key INLINE [

_Key

1

2

3

4

5

6

];

let vtableFieldToString=peek('_Key',0,'xx');

hth

Sasi

[simeonlukich]

Thank you very much for your reply. I feel like an idiot as i have used exist in the past but didn't think to use it this time.

[simeonlukich]

Thanks for that, that is useful