topic Sort by element of an array in FlexSim Forum

Sort by element of an array

marian_cretu — Thu, 30 Apr 2020 06:57:02 GMT

[ FlexSim 19.1.1 ]

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.

Re: Sort by element of an array

joerg_vogel_HsH — Thu, 30 Apr 2020 07:17:30 GMT

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

Re: Sort by element of an array

marian_cretu — Thu, 30 Apr 2020 07:35:33 GMT

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

Re: Sort by element of an array

joerg_vogel_HsH — Thu, 30 Apr 2020 07:36:21 GMT

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"));

Re: Sort by element of an array

joerg_vogel_HsH — Thu, 30 Apr 2020 07:39:13 GMT

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"));

Re: Sort by element of an array

marian_cretu — Thu, 30 Apr 2020 07:56:31 GMT

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.

Re: Sort by element of an array

joerg_vogel_HsH — Thu, 30 Apr 2020 08:08:21 GMT

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"));

Re: Sort by element of an array

marian_cretu — Thu, 30 Apr 2020 08:17:10 GMT

Great thank you!