question

Guillaume V avatar image
0 Likes"
Guillaume V asked Guillaume V commented

Please can someone help me write my QUERY?

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.

1626857968547.png

FlexSim 19.0.0
process flowquerysqlpullpullfromlist
1626857968547.png (165.7 KiB)
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

1 Answer

Felix Möhlmann avatar image
1 Like"
Felix Möhlmann answered Guillaume V commented

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.

pullwhensamelabel.png

testnblabeltestidenticalinlist_1.fsm


· 6
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Guillaume V avatar image Guillaume V commented ·
Thank you so much @Felix Möhlmann it works perfectly fine. I learnt so much thanks to this, have a great day.
0 Likes 0 ·
Guillaume V avatar image Guillaume V commented ·

@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?

0 Likes 0 ·
Felix Möhlmann avatar image Felix Möhlmann Guillaume V commented ·

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) 
0 Likes 0 ·
Guillaume V avatar image Guillaume V Felix Möhlmann commented ·

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...

0 Likes 0 ·
Show more comments