7 Replies Latest reply on Nov 29, 2018 2:37 PM by Zhouyi Zhang

    Comparing to get the common and the difference between two lists of strings

    ismaiel Altahan

      Hi all,

       

      I am trying to compare two list of string as below but I am not able to get the desired results.

       

       

      2016 Stores Ids
      current Stores Ids
      abc123abc123
      abc345
      abc678abc678
      abc999
      abc888

       

      The result i want is the Store status columns,
      This table explained the logic behind the result I need.

       

       

      2016
      2017Sales
      Store Status
      existexistSales > 0Store Still Selling
      existdoes not existClosed Stores
      existexistSales <=0Stores Not Selling
      does not existexistSales > 0New Stores with Sales
      does not existexistSales <= 0New Stores with No Sales

       

       

       

       

      I am using this in my calculated field.

       

       

      IF  SUM([Sales]) > 0  and  ATTR([current Stores Ids] = [2016 Stores Ids]) THEN 'Stores Still Selling'

      ELSEIF SUM([Sales]) <=0 and ATTR([current Stores Ids= [2016 Stores Ids]) THEN 'Stores Not Selling'

      ELSEIF  SUM([Sales]) <=0 and ATTR([current Stores Ids] != [2016 Stores Ids]) THEN 'Stores Not Selling'

      ELSEIF  SUM([Sales]) >0 and ATTR([current Stores Ids] != [2016 Stores Ids]) THEN 'New Stores'

      ELSE 'Green: New Stores'

      END

       

       

      I believe there is something wrong with my calculation especially in != part cus, it returns more than one result from both columns current and 2016 but the calculation is considering it as once if im not mistaken.
      and because those two lists are from different data source, I could not do this conditioning in sql level.

       

       

       

      Can anyone guide with the correct way please.


      Here is the workbook attached.

       

       

      Thanks in advance.