topic Re: Creating Custom Function To Write Contents Of Table Field To Variable in QlikView

Creating Custom Function To Write Contents Of Table Field To Variable

simeonlukich — Tue, 08 Sep 2015 04:26:07 GMT

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

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

...

];

NewTableWithRemovedValues:

LOAD *

RESIDENT SomeTable

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

Any help or alternate suggestions would be greatly appreciated.

Thanks

Simeon

Re: Creating Custom Function To Write Contents Of Table Field To Variable

marcus_sommer — Tue, 08 Sep 2015 12:33:58 GMT

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

Re: Creating Custom Function To Write Contents Of Table Field To Variable

sasiparupudi1 — Tue, 08 Sep 2015 14:07:31 GMT

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

];

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

hth

Sasi

Re: Creating Custom Function To Write Contents Of Table Field To Variable

simeonlukich — Tue, 08 Sep 2015 21:18:14 GMT

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.

Re: Creating Custom Function To Write Contents Of Table Field To Variable

simeonlukich — Tue, 08 Sep 2015 21:18:36 GMT

Thanks for that, that is useful