LETing multiple field values in a sciprt variable

[Gestion-PSD]

Hi all!

We're trying to store a list of values in a Qlik Sense Business  script variable.

Those values are the values of a field already loaded in script.

The destiny of the variable is to be used in a "Where in" SQL SELECT query.

How can We do it?

I mean (pseudocode):

TableA: LOAD FieldA, FieldB FROM wherever; //Load tableA with FieldB wich values would listed on vMyVariable

LET vMyVariable = read all FieldB values; //That's the question!!

TableB: SQL SELECT FieldC, FieldD FROM postgresdatabase where FieldD in ($(vMyVarialbe)); //Load TableB fields where FieldD values are equals to FieldB ones.

I think an approach would be to populate the variable as we're reading Table A fields one by one in a FOR loop:

LET vMyVariable='';  //Start the variable with no values

FOR i in NoOfRows(TableA) //Looping over all TableA rows

LET vMyVariable = vMyVariable & ',' & Peek(FieldB); //Insert FieldB value for i row into variable

NEXT

But, there'is another way to do it? a more elegant and/or economic one?

Best regards,

Alonso Torres

[Or · Accepted solution]

Load Concat(Field,',') as Fields INLINE [
Field
Value1
Value2
Value3
];

Let vValues = peek('Fields');

Note that this will only work for numeric values insofar as WHERE IN - if you want to use it for textual values, you'll need slap a chr(39) around each value during the concat() phase to comply with e.g. IN ('Hello','World').

As long as the list of values isn't particularly long, and I'm guessing it isn't, you could also use the loop approach as well without any serious impact to performance.

[Gestion-PSD]

Hi again!

Sorry for my quick repost, but I think I've found exactly what we're looking for:

FOR EACH i in FIELDVALUELIST('FieldB')
  LET vMyVariable= '$(vMyVariable)' & ',' & i;
NEXT;

And later, to erase first comma in the variable.

We need to test it, but it's promising.

Best regards,

Alonso Torres.

[Gestion-PSD]

Thaks Or for your quick reply.

We're going to test your approach, too.

We're talking about less to 200 rows in TableA. In fact. FieldB is numeric.

So, as I understand, you said:

TableA: LOAD FieldA, FieldB FROM wherever; //Load tableA with FieldB wich values would listed on vMyVariable.

STORE TableA; //We need this table in other context

IntermediateTable:

LOAD Concat(FieldB,',') AS FieldB_to_variable;

LET vMyVariable = PEEK(FieldB_to_variable);

DROP TABLES TableA,IntermediateTable;

TableB: SQL SELECT FieldC, FieldD FROM postgresdatabase where FieldD in ($(vMyVarialbe)); //Load TableB fields where FieldD values are equals to FieldB ones.

It seems interesting.

Thanks!!

[Gestion-PSD]

Hi again.

As far as we've tested, your solution is much better than ours.

Thanks!

[Or]

Yup, you can loop through fieldvaluelist() as well. As I said, with only 200 values, I wouldn't expect it to take a long time using any method. Also note that if the values are textual, you will still need to get the quotes in unless they're already part of the text string, using chr(39) & '$(vMyVariable)' & chr(39), I think.

[amandalyst]

Using a combination of the above answers, here's what I came up with. This is assuming that the field you want string-a-fied is called FieldB, FieldB is a text value, and it's stored in table TableA:

LOAD
CONCAT(FieldB, chr(39) & ',' & chr(39)) as FieldBString
RESIDENT TableA;

LET myV2 = chr(39) & FieldValue('FieldBString',1) & chr(39);

And at the end of the SQL Load:

WHERE FieldB IN ($(myV2));

Also - this will only work with <1000 values.