Beast Mode for Getting the order status of the vehicles on order

ozarkram
ozarkram Member
in Magic ETL

I am trying to determine the status of the vehicle on order -Whether it is stock or Sold , whether it is part of the BAM campaign or not and the sold or stock date based on the following set of rules

  1. If the data column orderstatus has *SLD ,SLD or SOLD then the comment column is sold ; if the orderstatus column has STOCK or *STOCK or STK followed by date (like STK 05/13) or *STK followed by date then the comment column is stock
  2. if the orderstatus column has * or BAM or any date (For ex: 5/13,05/17/2022) then the BAMYN column would be BAM
  3. For the Soldorstockdate output column if the column orderstatus has a date then that date would be the soldorstock date otherwise the date in the column orddate would be the Soldorstockdate

| Segment | mmodel | brand | orderstatus         | orddate  |

| ------- | ------- | ------ | ---------------------------- | --------- |

| maka  | M12E4  | Nimg  | \*SLD 05/11/2022       | 5/1/2022 |

| sika  | KL6781 | Cheung | 37141 SLD BAM        | 5/5/2022 |

| kloi  | NB1290 | Vloti | 1277489 STK#39298.32831   | 5/4/2022 |

| Ping  | BN1289 | gower | 36888 SLD FLOREN ANGEL    | 5/6/2022 |

| Melow  | VB1901 | operw | 1286664 051222        | 5/10/2022 |

| Bekow  | XC901  | mewar | \*SLD 5/14/22 Heman     | 5/3/2022 |

| Nakin  | JH121  | korew | STOCK            | 5/16/2022 |

| Verura | CV123  | thilla | 1287002 LONGMINT       | 5/12/2022 |

| Chaluli | BN8901 | dora  | STOCK BAM 5/17/22      | 5/11/2022 |

| Kroger | XC123  | iops  | \*STOCK BAM 5/23/22     | 5/8/2022 |

| beqow  | VB123  | pirar | 3902120 STOCK        | 5/20/2022 |

| Viast  | NM41W  | kolpe | SOLD BRANDON BOX 36790    | 5/15/2022 |

| Chimmin | BN123  | tyrow | STK 5/13           | 5/3/2022 |

| Bellow | Vio23  | Callow | \*STK 5/13/22        | 5/5/2022 |

| Nalla  | Krowmin | Gilqa | 37938 STOCK 5/18/22 PER SARA | 5/18/2022 |


| Segment | mmodel | brand | orderstatus         | orddate  | comment | BAM-Yes or no | Soldorstockdate |

| ------- | ------- | ------ | ---------------------------- | --------- | ------- | ------------- | --------------- |

| maka  | M12E4  | Nimg  | \*SLD 05/11/2022       | 5/1/2022 | Sold  | BAM      | 5/11/2022    |

| sika  | KL6781 | Cheung | 37141 SLD BAM        | 5/5/2022 | Sold  | BAM      | 5/5/2022    |

| kloi  | NB1290 | Vloti | 1277489 STK#39298.32831   | 5/4/2022 |     |        |         |

| Ping  | BN1289 | gower | 36888 SLD FLOREN ANGEL    | 5/6/2022 | Sold  |        | 5/6/2022    |

| Melow  | VB1901 | operw | 1286664 051222        | 5/10/2022 |     |        |         |

| Bekow  | XC901  | mewar | \*SLD 5/14/22 Heman     | 5/3/2022 | Sold  | BAM      | 5/14/2022    |

| Nakin  | JH121  | korew | STOCK            | 5/16/2022 | Stock  |        | 5/16/2022    |

| Verura | CV123  | thilla | 1287002 LONGMINT       | 5/12/2022 |     |        |         |

| Chaluli | BN8901 | dora  | STOCK BAM 5/17/22      | 5/11/2022 | Stock  | BAM      | 5/17/2022    |

| Kroger | XC123  | iops  | \*STOCK BAM 5/23/22     | 5/8/2022 | Stock  | BAM      | 5/23/2022    |

| beqow  | VB123  | pirar | 3902120 STOCK        | 5/20/2022 | Stock  |        | 5/20/2022    |

| Viast  | NM41W  | kolpe | SOLD BRANDON BOX 36790    | 5/15/2022 | Sold  |        | 5/15/2022    |

| Chimmin | BN123  | tyrow | STK 5/13           | 5/3/2022 | Stock  | BAM      | 5/13/2022    |

| Bellow | Vio23  | Callow | \*STK 5/13/22        | 5/5/2022 | Stock  | BAM      | 5/13/2022    |

| Nalla  | Krowmin | Gilqa | 37938 STOCK 5/18/22 PER SARA | 5/18/2022 | Stock  | BAM      | 5/18/2022    |

Can you please help me here

Sample data sheet.xlsx


Tagged:

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    @ozarkram - I was able to get the Comment and BAM columns to work, but the Sold_or_Stock_Date column is proving to be quite difficult in beastmode. Can you use ETL to manipulate the data?

    Comment

    case 
	when `orderstatus` LIKE '%*SLD%' then 'Sold'
    when `orderstatus` LIKE '%SLD%' then 'Sold'
    when `orderstatus` LIKE '%SOLD%' then 'Sold'
    when `orderstatus` LIKE '%STOCK%' then 'Stock'
    when `orderstatus` LIKE '%*STOCK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    when `orderstatus` LIKE '%STK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    when `orderstatus` LIKE '%*STK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    END

    BAM

    CASE 
	when `orderstatus` LIKE '%*%' then 'BAM'
    when `orderstatus` like '%BAM%' then 'BAM'
    when `orderstatus` LIKE '%/%/%' then 'BAM'
    when `orderstatus` LIKE '%/%' then 'BAM'
    END



    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    @ozarkram - I was able to get the Comment and BAM columns to work, but the Sold_or_Stock_Date column is proving to be quite difficult in beastmode. Can you use ETL to manipulate the data?

    Comment

    case 
	when `orderstatus` LIKE '%*SLD%' then 'Sold'
    when `orderstatus` LIKE '%SLD%' then 'Sold'
    when `orderstatus` LIKE '%SOLD%' then 'Sold'
    when `orderstatus` LIKE '%STOCK%' then 'Stock'
    when `orderstatus` LIKE '%*STOCK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    when `orderstatus` LIKE '%STK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    when `orderstatus` LIKE '%*STK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    END

    BAM

    CASE 
	when `orderstatus` LIKE '%*%' then 'BAM'
    when `orderstatus` like '%BAM%' then 'BAM'
    when `orderstatus` LIKE '%/%/%' then 'BAM'
    when `orderstatus` LIKE '%/%' then 'BAM'
    END



    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ozarkram
    ozarkram Member

    Thank you so much @ST_-Superman-_ ...Really appreciate your help!...

Categories