Please can someone help me write my QUERY?

[guillaume_v]

[ FlexSim 19.0.0 ]

Please help me, I am stuck...
I want to pull x2 tokens only if they have the same label "TEST" value in the list.
I do not care what value it is, just the fact that they have the same label value means that I want to pull them.

Any idea? I tried the function "COUNT()"but it didn't seem to be recognised by the Pull From List activity.

[moehlmann_fe · Accepted solution]

I don't know if you achieve this in the pull activity alone. I can offer a workaround though.

First, instead of using an internal list I created a global list for the entries so I can query it with "Table.query()". In the Assign Labels activity I search for labels that appear more than once on the list. If successful, that value gets returned and assigned to the "LabelVar" label on the token. If not, a null value gets returned.

// Query for labels with more than one entry
Table Identicals = Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > 1");

if(Identicals.numRows > 0)
{
    // If any are found return the first one
    return Identicals[1][1];
}

// Else return null
return nullvar;

Based on wether the label contains a valid value, the token then either waits for another value to be pushed to the list or continues on to the pull activity where it pulls the values based on the "LabelVar" label.

testnblabeltestidenticalinlist_1.fsm

[guillaume_v]

Thank you so much @Felix Möhlmann it works perfectly fine. I learnt so much thanks to this, have a great day.

[guillaume_v]

@Felix Möhlmann 1 last question, please.

I am not used to writing custom codes and it scares me a little.


I would like to change this line:

Table Identicals = Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > 1");

and instead of testing if it is > 1, I would like to test if it is > a label value in token.
example:
Table Identicals = Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > token.number");

I get an error when I write it like that.
How would you recommend writing the custom code, please Felix?

[moehlmann_fe]

You have to pass in the value as a parameter to the query command, like this:

Table.query("SELECT LABELTEST FROM List1 GROUP BY LABELTEST HAVING COUNT(LABELTEST) > $1", token.number) 

[guillaume_v]

Thanks @Felix Möhlmann

One very very last question:

I tried playing a little bit with the parameters. I would need to use a parameter as well for the Column Name (here LABELTEST).
I tried writing the Query like this:

Table.query("SELECT $1 FROM List1 GROUP BY $1 HAVING COUNT($1) = $2", token.QN, token.Q);

But when I do this, the returned variable stored in the "LabelVar" becomes the column name. I am very confused...

The problem seems to be the $1 = token.QN but I don't understand why.
I checked and in my model the value stored in the label QN of the tokens does correspond to the Column Name in the List1. I'm so confused...

[moehlmann_fe]

The column name can/must be directly inserted into the query string. You can add strings (Text) together with the + operator.

Table.query("SELECT [" + token.QN + "] FROM List1 GROUP BY [" + token.QN + "] HAVING COUNT([" + token.QN + "]) > $1", token.Q);

This will be interpreted as if you typed the name in token.QN directly into the query.

Like this, if the value of token.QN was still "LABELTEST".

Table.query("SELECT ["LABELTEST"] FROM List1 GROUP BY ["LABELTEST"] HAVING COUNT(["LABELTEST"]) > $1", token.Q);

The square brackets are only necessary if the column name in token.QN might contain a space.

In fact, instead of passing the number in as a parameters you could theoretically also write the end of the query as

... > " + string.fromNum(token.Q, 0)

converting the number into a string an inserting it directly into the query.

[guillaume_v]

You are a wizard @Felix Möhlmann thank you.