Forum Discussion

CoreyP's avatar
CoreyP
Solution Sage
6 years ago
Solved

M Query Editor - Trouble with Conditional IF statement

Hey guys,

 

I have a dataset that is a transaction log of pallet movements within a warehouse. It has the following fields: Date, Pallet No., From Location, To Location. I am adding a conditional column to the dataset to identify what activity was taking place. Rules set on From Location and To Location determine the activity. All of my rules are working correctly except for one, and I can't figure out why. I'm hoping extra sets of eyes might see something I'm missing. 

 

Here is my entire column code: 

 

if 
[FROM_LOCATION] = "SPRCV"
and [TO_LOCATION] <> "SG-PRO-CESS"
and [TO_LOCATION] <> "OS-PUT-AWAY"
and [TO_LOCATION] <> "PUT-AWA-50"
then "Data Entry"
else


if 
[FROM_LOCATION] = "SPRCV"
and [TO_LOCATION] = "OS-PUT-AWAY" 
then "OS Dimming"
else


if 
[FROM_LOCATION] = "SG-PRO-CESS"
then "SG Data Entry"
else

if 
[FROM_LOCATION] = "DANGR-GOODS" 
or [TO_LOCATION] = "PUT-AWA-50"
then "Complex Data Entry"
else


if 
[FROM_LOCATION] = "PUT-AWA-40"
then "Declarations"
else


if 
[TO_LOCATION] = "REPCK-ENTRY"
then "Repack"
else


if 
Text.StartsWith([TO_LOCATION] , "A0")
or Text.StartsWith([TO_LOCATION] , "B0") 
then "Putaway" 
else


if 
[TO_LOCATION] = "PICK-ED1"
then "Loose Pick"
else


if 
[TO_LOCATION] = "SM-CON-SOL"
or [TO_LOCATION] = "NZ-CON-SOL"
then "Complex Pick"
else 

if 
Text.StartsWith([FROM_LOCATION], "REP")
and Text.StartsWith([TO_LOCATION], "PAS")
or Text.StartsWith([TO_LOCATION], "PUT")
then "Repack Entry"

else
"Other"

 

 

The specific activity I'm having trouble with is Repack Entry:

 

if 
Text.StartsWith([FROM_LOCATION], "REP")
and Text.StartsWith([TO_LOCATION], "PAS")
or Text.StartsWith([TO_LOCATION], "PUT")
then "Repack Entry"

else

 

The logic should read: If From Location starts with REP and To Location starts with PAS or PUT, then Repack Entry. 

The From Location beginning with REP should return only two values, REPCK-ENTRY and REP-ACK-ING ( If From Location equals REPCK-ENTRY or REP-ACK-ING and To Location starts with PAS or PUT, then Repack Entry ), however, when I validate I'm getting a lot of values in From Location that do not start with REP. 

 

Does anyone have any thoughts? 

Any help is MUCHO appreciated!

Thanks!

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi CoreyP ,

     

    "The logic should read: If From Location starts with REP and To Location starts with PAS or PUT, then Repack Entry. "

    Try this code:

    if 
        Text.StartsWith([FROM_LOCATION], "REP") 
        and 
        (
        Text.StartsWith([TO_LOCATION], "PAS")
        or Text.StartsWith([TO_LOCATION], "PUT")
        )
        then "Repack Entry" else null)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi CoreyP ,

     

    "The logic should read: If From Location starts with REP and To Location starts with PAS or PUT, then Repack Entry. "

    Try this code:

    if 
        Text.StartsWith([FROM_LOCATION], "REP") 
        and 
        (
        Text.StartsWith([TO_LOCATION], "PAS")
        or Text.StartsWith([TO_LOCATION], "PUT")
        )
        then "Repack Entry" else null)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi CoreyP 

     

    Looks like your 'Repack' IF statement is simply missing a set of parentheses.

    Try this:

    if 
Text.StartsWith([FROM_LOCATION], "REP")
and (Text.StartsWith([TO_LOCATION], "PAS")
or Text.StartsWith([TO_LOCATION], "PUT"))
then "Repack Entry"

else

     

    Best regards,

    Martyn