How to find a row in a table, and extract a value according to the item label?

Question

question

TOTH-EM asked Jun 23, '17 TOTH-EM commented Jun 26, '17

How to find a row in a table, and extract a value according to the item label?

Hi,

I have a table with some boxes that are labeled with an ID (box_id) and they all go through the same processor.

I also have a table with two collumns (Box_id and processing time) [cf. image].

How can I make my procecssor have a processing time that corresponds, regarding the table?

I naively tried this :

Table qer = Table.query("SELECT [Processing time] FROM [Magical Boxes] WHERE BOX_id == $1", item.labels["box_id"]);
return qer[1];

but it didn't work. I also tried to label the processor with the corresponding value, but the problem seems to come from the fact I cannot get the value from the table with the query.

Thanks,

--- EDIT ---

Using this in the processor worked.

query("FROM [Magical Boxes] WHERE BOX_id == $1", item.box_id);
return Table("Magical Boxes")[getquerymatchtablerow("Magical Boxes",1)][2];

--- EDIT 2 ---

After Mischa Spelt's comment, a better way would be to simply return qer[1][1] (faster and the first uses a deprecated command)

Software Version:

FlexSim 17.1.2

query sql table commands

bx-query.png (4.7 KiB)

5 |100000

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

Answer 1 · 2017-06-23T15:38:09Z

Steven Hamoen answered Jun 23, '17 TOTH-EM commented Jun 26, '17

@TOTH-EM I think that item.labels() returns a pointer to the label and you need the value so I guess that item.box_id would be better here.

· 3

5 |100000

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

TOTH-EM commented · Jun 26, 2017 at 07:56 AM

Thanks. That was indeed my error. Though the code I posted doesn't seem to work, I managed to solve it using getquerymatchtablerow().

0 ·

Steven Hamoen TOTH-EM commented · Jun 26, 2017 at 08:22 AM

@TOTH-EM Have you seen the last last comment by @Mischa Spelt?

0 ·

TOTH-EM Steven Hamoen commented · Jun 26, 2017 at 08:32 AM

Yes and I think I have an idea why my code didn't work now

0 ·

Answer 2 · 2017-06-26T07:45:26Z

Mischa Spelt answered Jun 26, '17 TOTH-EM commented Jun 26, '17

Since qer is a table, you need to get the value from the first row, first column:

return qer[1][1];

· 4

5 |100000

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

TOTH-EM commented · Jun 26, 2017 at 08:30 AM

I solved my problem, but I'm curious, why would selecting one collumn ([Magical Boxes])return a 2 dimensional table?

Oh wait, it's probabely the indexing, totally forgot about it. Thanks

0 ·

Mischa Spelt TOTH-EM commented · Jun 26, 2017 at 09:21 AM

By the way, I saw your solution in the edit to the original post: note that this is relatively slow (first you do a query that already gives you the result, but you ignore it and have to go back to the table to read the result again) and also it uses the deprecated command getquerytablematchrow (the new way is SELECTing the ROW_NUMBER as one of the result columns). I would recommend just getting the result from the query like I described, or if you don't like/understand the query well enough, go with Clair's suggestion of findmatch.

0 ·

TOTH-EM Mischa Spelt commented · Jun 26, 2017 at 10:03 AM

That's what I ended up doing when I noticed the command is deprecated anyway and that your fix works. I didn't notice it was slower though, so it's good to know.

I edited my question once again to point it out.

Concerning to findmatch, I just tried it and it worked perfectly fine too.

Which one is faster? findmatch() or the query?

0 ·

Mischa Spelt commented · Jun 26, 2017 at 09:19 AM

In your case, the WHERE also gives you just one matching object, so the result table will have one row, so you could also ask why you need indexing at all: why doesn't the query just give back a single value?

In fact, a SELECT query always returns a table. If you only select one column, this will be a table with one column. (One-column) tables are different from arrays and FlexSim doesn't automatically turn one into the other.

0 ·

Answer 3 · 2017-06-23T15:18:09Z

Clair A answered Jun 23, '17 TOTH-EM commented Jun 26, '17

Hello,

I think that the findmatch command is what you're looking for.

· 1

5 |100000

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

TOTH-EM commented · Jun 26, 2017 at 10:04 AM

Thanks, that worked perfectly too

0 ·

question