assigning priority according to cat number

[Hussein_Osseily]

Hello Community

I am having a hard time in working this out or how to think of a way to acheive this. 

I have a table that has 5 Categories and its priority (1 to 5) 
Cat 1 = Priority 1 
Cat 2 = Priority 2 

Cat 3 = Priority 3

Cat 4 = Priority 4

Cat 5 = Priority 5

BUT 

if there are no shipments of the Cat 1 , that makes Cat 2 Priority 1 and Cat 3 Priority 2 and so on 

another example, If there are no shipments of the Cat 3 , that makes Cat 4 priority 3 ,...

the only thing i could think of is using the IF and lookup , but i have no clue how to think of the cases where one of the cats would be missing which means prio -1 or -2 

i would really appreciate any input of how to acheive this. 

thanks

[Steve · Accepted solution]

Maybe this (untested)?

INDEX(
  LIST("p1", "p2", "p3", "p4", "p5"),
  (
    COUNT(
      SPLIT(
        ANY(
          SPLIT(
            (" , " & SORT(table[category] - LIST("")) & " , "),
            (" , " & [category] & " , ")
          )
        ),
        " , "
      )
    )
    + 1
  )
)

Replace table with the name of the table containing the shipments; category (x2) with the name of the column containing the shipment's category; and p1 through p5 with the desired priority values.

INDEXOF() - Google Cloud Community

[Suvrutt_Gurjar]

May we know if this understanding is correct that each row in the table has a category column and then there will be a computed priority column in each row as per the logic you described.

[Hussein_Osseily]

yes, its per row. 
a cat column do determin the cat 1,2,3,4,5 
and auto computed PRIO column

[gregdiana1]

I suggest you do a select on the categories and filter all rows, or create a list, which are non-zero shipments. Then use the index of the filtered list to create the priority. This is just a quick off the top of my head suggestion. 

[Steve · Accepted solution]

Maybe this (untested)?

INDEX(
  LIST("p1", "p2", "p3", "p4", "p5"),
  (
    COUNT(
      SPLIT(
        ANY(
          SPLIT(
            (" , " & SORT(table[category] - LIST("")) & " , "),
            (" , " & [category] & " , ")
          )
        ),
        " , "
      )
    )
    + 1
  )
)

Replace table with the name of the table containing the shipments; category (x2) with the name of the column containing the shipment's category; and p1 through p5 with the desired priority values.

INDEXOF() - Google Cloud Community

[Hussein_Osseily]

@Steve Thanks for your response, 
this have resulted the following: 
Cat 
  1 =    P2                    
  2 =   P3                    
  3 =   P4                    

  4 =   P5                   
  5 =   BLANK             

[Steve · Accepted solution]

Hmm! Try removing the + 1 from the expression.

[Hussein_Osseily]

Removed the +1 

working like magic.

Thanks for your help

[Suvrutt_Gurjar]

Nice one @Steve