Sort by element of an array

Question

question

Marian Cretu asked Apr 30, '20 Marian Cretu commented Apr 30, '20

Sort by element of an array

Hello guys,

Is it possible to sort a query result by an element inside an array ?

The above table is the result of a query from a larger table. Is it possible to add a condition to the query that it will sort by the first element of the Array so I can have the following result ?

Thank you.

Software Version:

FlexSim 19.1.1

flexsim 19.1.1

capture.jpg (13.7 KiB)

capture-result.jpg (13.7 KiB)

· 2

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.

Joerg Vogel commented · Apr 30, 2020 at 07:17 AM

@Marian Cretu, next time please add a model. It is so time consuming to build your image table myself.

0 ·

Marian Cretu Joerg Vogel commented · Apr 30, 2020 at 07:35 AM

Sorry Jorg. Here is the file. Thank you so much.sort by array element.fsm

0 ·

sort-by-array-element.fsm (30.0 KiB)

Answer 1 · 2020-04-30T07:36:21Z

Joerg Vogel answered Apr 30, '20 Marian Cretu commented Apr 30, '20

Table.query("SELECT $2 AS rack, $3 AS pallet_details FROM $1 ORDER BY $4 ASC",    
   /*$1*/Table("GlobalTable1").numRows,    
   /*$2*/Table("GlobalTable1")[$iter(1)][1],    
   /*$3*/Table("GlobalTable1")[$iter(1)][2],    
   /*$4*/Table("GlobalTable1")[$iter(1)][2].as(Array)[1]).cloneTo(Table("dump"));

table-query-by-array-element.png (47.5 KiB)

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

Joerg Vogel commented · Apr 30, 2020 at 07:39 AM

much shorter:

Table.query("SELECT $2 AS rack, $3 AS pallet_details FROM $1 ORDER BY $3[1] ASC",    
  /*$1*/Table("GlobalTable1").numRows,    
  /*$2*/Table("GlobalTable1")[$iter(1)][1],    
  /*$3*/Table("GlobalTable1")[$iter(1)][2]).cloneTo(Table("dump"));

1 ·

Marian Cretu Joerg Vogel commented · Apr 30, 2020 at 07:56 AM

Hello Jorg, Thank you for your answer. It sorts correctly. On this query I need to also add a condition WHERE rack="1" but it doesnt seem to work. Can you please advise ? Thank you.

0 ·

Joerg Vogel Marian Cretu commented · Apr 30, 2020 at 08:08 AM

Table.query("SELECT $2 AS rack, $3 AS pallet_details FROM $1 WHERE rack = 1 ORDER BY $3[1] ASC",    
  /*$1*/Table("GlobalTable1").numRows,    
  /*$2*/Table("GlobalTable1")[$iter(1)][1],    
  /*$3*/Table("GlobalTable1")[$iter(1)][2]).cloneTo(Table("dump"));

1 ·

Show more comments

question