Forum Discussion

JesperBT's avatar
JesperBT
Frequent Visitor
3 years ago
Solved

Conditional column between queries

Hello everyone, I would like to create a conditional column that is based on different tables. I know that you are able to merge tables and from there create a conditional column that is based on th...
  • Bassehave's avatar
    Bassehave
    3 years ago

    DimaMD 

    Thanks a lot for your help. I managed to fiddle a bit with the code you provided and I actually found a way to do it all with a single column.

     

    COM_DeliveryPMA = 
SWITCH(
    TRUE(),
    VALUE(IF(right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)) = "SUPPLIER", "0", right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)))) = LOOKUPVALUE('table pma'[PMA Number],'table pma'[Postcode],'data cfb'[Zip Code]), 1,
    VALUE(IF(right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)) = "SUPPLIER", "0", right([Ship Node], len([Ship Node]) - search(".",[Ship Node],,0)))) in {681,76,405}, 2,
    0)

     

    The reason for the IF command, is that I found an issue with the Ship Node column, some of the values are "SUPPLIER" which I can't convert to a number.

    So I did a VALUE and changed all of the "SUPPLIER" to "0" and then using the LOOKUPVALUE inside of the SWITCH.

     

    This is just the solution I wanted, so thank you very much!

    It seems I'm not able to mark your reply as a solution.

DimaMD's avatar
DimaMD
Solution Sage
3 years ago

Hi JesperBT You provided slightly incorrect data, but according to your expected result it was possible to output your expected result, see the file

  • Bassehave's avatar
    Bassehave
    Helper I
    3 years ago

    Hej DimaMD,

     

    Thanks a lot for taking the time to help me, I really appreciate it.

    I'm just curious, what data did you find that was incorrect?

     

    I will try to convert it to my original dataset and see the result, but I think it will work.

     

    J

    • DimaMD's avatar
      DimaMD
      Solution Sage
      3 years ago

      Hi, Bassehave Sales Order Number and Ship Node in the data cfb table do not match with your result that you provided

      • Bassehave's avatar
        Bassehave
        Helper I
        3 years ago

        Hej DimaMD,

        Good catch! I had to manually change some cells to get a viable dataset on small scale, but as you said, it shouldn't matter when I used the formula in the original file. Thanks a lot!

         

        J