9 Replies Latest reply on Jul 25, 2013 8:44 AM by disabled_xaviervb

    Lists

      Hello,

       

      I'm trying to make a script that will avoid repeating the same things in lists for me.

       

      I have an variable amount of lists: exemple:

       

      list 1 : A B C D

      list 2 : A B C E

      list 3 : A B C

      list 4 : A B C F G

       

      I would like the output to be:

       

      list 1, 2, 3, 4 : A B C

      list 1 : D

      list 2 : E

      list 4 : F G

       

      Thanks

       

      Xavier

        • 1. Re: Lists
          taylorsharpe

          Are these lists in a variable array or fields in a table  Is there a single field called "List" with the alphabetical values in it or is this a found set?  If you could attach a file with the example on it, I'm sure we can help you out. 

          • 2. Re: Lists

            Hi Taylor

             

            Those lists are variables($) I fill with Agnes Barouh's CustomList ( Start ; End ; Function )

             

            I'm looking for way to approach the problem. Should I start by comparing the first two lists/variables, keep the Intersection set and continue with the next list, compare it etc etc or is there an easier way?

             

            Thanks

             

            Xavier

            • 3. Re: Lists
              taylorsharpe

              What does the raw variable look like? 

               

                  

              list 1 : A B C D

              list 2 : A B C E

              list 3 : A B C

              list 4 : A B C F G

               

              or

               

              A B C D

              A B C E

              A B C

              A B C F G

               

               

              Will the lists always be incremental (1, 2, 3, 4, 5) or will this vary (1, 3, 2, 5, 6)?

               

              I assume you want to compare each list (value) with all the other ones. 

              • 4. Re: Lists

                I was gessing the raw variable would be A¶B¶C¶D

                But if adding a name would make it easier that's possible too.

                 

                The lists are incremental

                 

                Yes, each list has to be compared with all others

                • 5. Re: Lists
                  taylorsharpe

                  So you have 4 variables:  $List1, $List2, $List3, $List4 populated by single capital letters of varying number of entries.  Or what I originally though you had was a single variable and each line represented a list with varying lengths of letters.  But back to the original assumption, 4 variables and only 4 variables (never 5 or 3, etc.).  Or you can have varying numbers of variables, it just involves a looping solution. 

                  • 6. Re: Lists
                    mbraendle

                    Maybe we can solve with some matrix formalism.

                    First you have to find out the maximum length of a row vector, which is the number of different items (e.g. one can throw all items in a return-delimited list and use a Deduplicate custom function, then use ValueCount).

                     

                    In your case, it is 7, since you have 7 different letters (it could also be words).

                     

                    This gives a 4x7 matrix. If an item does not exist in your row vector, we represent it as a 0.

                    The column numbers serve as index to your items.

                     

                     

                          1 2 3 4 5 6 7

                     

                    1    A B C D 0 0 0

                    2    A B C 0 E 0 0

                    3    A B C 0 0 0 0

                    4    A B C 0 0 F G

                     

                    Then for each row, we replace the non-zero items with its row number:

                     

                          1 2 3 4 5 6 7

                     

                    1    1 1 1 1 0 0 0

                    2    2 2 2 0 2 0 0

                    3    3 3 3 0 0 0 0

                    4    4 4 4 0 0 4 4

                     

                    Then we transpose the matrix  (or read it not from left to right, but from top to bottom):

                     

                     

                    1    1 2 3 4

                    2    1 2 3 4

                    3    1 2 3 4

                    4    1 0 0 0

                    5    0 2 0 0

                    6    0 0 0 4

                    7    0 0 0 4

                     

                    and replace the row index with its corresponding item

                     

                    A   1 2 3 4

                    B   1 2 3 4

                    C   1 2 3 4

                    D   1 0 0 0

                    E   0 2 0 0

                    F   0 0 0 4

                    G  0 0 0 4

                     

                    Then one can sort the matrix rows:

                     

                    F  0 0 0 4

                    G  0 0 0 4

                    E  0 2 0 0

                    D  1 0 0 0

                    A  1 2 3 4

                    B  1 2 3 4

                    C  1 2 3 4

                     

                    Then one loops through the rows and compare each row to the previous row: If it's different, a new line will be opened and the item added, if it's the same, only the item added:

                     

                    0 0 0 4 => F G

                    0 2 0 0 => E

                    1 0 0 0 => D

                    1 2 3 4 => A B C

                     

                    Now remove the 0 (zero) and you have the solution:

                     

                    4: F G

                    2: E

                    1: D

                    1 2 3 4: A B C

                    1 of 1 people found this helpful
                    • 7. Re: Lists
                      DrewTenenholz

                      Xavier --

                       

                      I could solve what I think is your question with Ray Cologon's excellent XORValues ( ListA ; ListB ) custom function. http://www.briandunning.com/cf/39

                       

                      I haven't seen the custom function you are using, but it sounds like it generates a set of $variables based on the input, creating as many $List[n] variables as needed each having as long a list of values as you require.  Sounds like a nice recursion.

                       

                      1)  One thing you will certainly need is the concatenation of all of the variables collected.  Maybe you can modify the custom function to also create a $ListComplete along with the individual items.  If not, create some sort of loop that puts them all together and don't fuss too much about the order or duplication of items.

                       

                      2)  Then, you can get the unique $ListCompleteUnique

                      Set Variable [$ListCompleteUnique ; XORValues ( $ListComplete ; "" ) ]  // yes, use nothing for ListB

                       

                      That gets you "A B C D E F G"

                       

                      3) Next, loop through your $List[n] variables  (and it would help if you have a count of them) and gather up the values which are in the $ListCompleteUnique that are NOT in each specific one.  For example:

                       

                      XORValues ( $List[1] ; $ListCompleteUnique ) = "E F G"

                      XORValues ( $List[2] ; $ListCompleteUnique ) = "D F G"

                      etc.

                       

                      The combination of all of these across your $List[n] variables will give you " E F G   D F G    F G" which I'll call $xLIst

                       

                      Then, XORValues ( $xList ; "" ) = "D E F G" which I'll call $xListUnique

                       

                      4) Next, you can find the values COMMON to all of your $List[n] with:

                      $Common= XORValues ( $xListUnique ; $ListCompleteUnique ) = "A B C"

                       

                      So, you now have the first part you were looking for.

                       

                       

                      5) Lastly, loop again through $List[n] with;

                       

                      $UniqueToME[n] = XORValues ( $List[n] ; $Common )

                       

                      Which should give:

                      $UniqueToME[1] = "D"

                      $UniqueToME[2] = "E"

                      $UniqueToME[3] = ""

                      $UniqueToME[4] = "F G"

                      ...

                       

                      I ran a test (without any looping, just in the data viewer), and this does work.

                       

                      Good Luck,

                      Drew Tenenholz

                      • 8. Re: Lists

                        Thank you, this is really an awesome solution. I'm testing it right now :-)

                        • 9. Re: Lists

                          Thank you for your reply. I'm having a look at Ray Cologon's  XORValues ( ListA ; ListB ) now.