1 2 Previous Next 17 Replies Latest reply on Apr 2, 2017 6:37 PM by user19752

    Combine value lists, all combinations

    Henry_2

      Hi,

       

      When having three value lists, eg.:

      List1,     List2,     List3
      ----       ----       ----
      A          Z1          001
      B          Z2          002
                 Z3         

               

      I'm struggling how to create a value list containing all combinations:

      A,Z1,001

      A,Z1,002

      A,Z2,001

      A,Z2,002

      A,Z3,001

      A,Z3,002

      B,Z1,001

      B,Z1,002

      B,Z2,001

      B,Z2,002

      B,Z3,001

      B,Z3,002

       

      (the comma could be any delimiter)

       

      I likely can create it by using a loop on List1, with nested loops for List2 and List3. That feels like I'm using a lot of script steps for something that is likely possible to solve with just a few steps.

       

      Anyone with an idea how to solve this with less script steps?

        • 1. Re: Combine value lists, all combinations
          beverly

          That's easy if you use the cartesian join in ExecuteSQL, but the "lists" should be records with field(s) from which to draw.

          How are your value lists created?

          beverly

          • 2. Re: Combine value lists, all combinations
            Henry_2

            Thanks for your quick response. Each list contains the records from a field (from three tables). Currently I create the lists by three ExecuteSQL queries (in a Let function). Making a cross join query sounds like the efficient way to go indeed, could you help me with a query example?

            • 3. Re: Combine value lists, all combinations
              beverly

              SELECT table1.field1, table2.field2, table3.field3

              FROM table1, table2, table3

              ... (If you need to narrow by WHERE, it goes here)

               

              The FROM with a comma-delimited list and no JOIN...ON will make the query a cartesian "join" and all values from all tables will be used.

              This then is Comma-separated and return delimited if you use the defaults:

              ExecuteSQL(_query ; "," ; "" )

               

              Like this example:

              beverly

              1 of 1 people found this helpful
              • 4. Re: Combine value lists, all combinations
                Henry_2

                Thanks Beverly,

                 

                Select query for cartesian product works well

                The select query works very well. Glad to see there is such an easy solution.

                 

                (Even learned that adding some other fields of table1,2,3, worked as needed (meaning adding additional fields of used tables did not result in additional combinations). Also added CASE and WHEN clauses to get exactly the output I needed)

                 

                Challenge importing to create new records

                I do run into a challenge going further. The reason I build the table is to export it (tab separated) and then import into an existing table to generate new records. As you also describe here: Nested Loop - need some help (free beer)

                 

                Exporting and importing seems to work, way too slow however (I did not check, but probably well over an hour).

                The number of records is almost 1mln. In the target table I added a compound key field (it auto enters the three key fields) with unique validation (selected to always check) to prevent duplicates.

                 

                So I thought how can I solve the performance issue:

                Option 1 - compound key in import file

                The import would likely be faster if I would not need to auto-generate the compound in the target table, but can add the compound as a column in the importfile (so on import it would only need to validate uniqueness, without the need to autoenter a compound key).
                If this would be the/a way to go, how could I get this output in my import file?

                col1  col2   col3    col4(compound)

                A     Z1     001     A-Z1-001

                A     Z1     002     A-Z1-002

                A     Z2     001     A-Z2-001


                I tried this:
                SELECT table1.field1, table2.field2, table3.field3, table1.field1 + '-' +table2.field2 +'-' +table3.field3
                but the output did not give the correct compound keys

                 

                Option 2 - generate import file with new records only

                Most likely by far the most performance improvement would be reached by not having existing records in the generated import file.  I don't see just yet how to (efficiently) generate the table/file to import without existing records.

                 

                Any idea's to efficiently create new records?

                • 5. Re: Combine value lists, all combinations
                  beverly

                  OPTION1

                  SELECT table1.field1, table2.field2, table3.field3, table1.field1 + '-' +table2.field2 +'-' +table3.field3

                  possibly the "+" is trying to add the values, so use "|" instead:

                  SELECT table1.field1, table2.field2, table3.field3, table1.field1|'-'|table2.field2|'-'|table3.field3 AS compoundKey

                  (the AS is the temporary column name)

                  but if you are getting an error or other "results", please reply with details.

                   

                  OPTION2

                  "without existing records" - whoa that's difficult. SQL SELECT can get table.field or constants or functions (such as CURDATE)

                  So... perhaps something else in your structure needs to be revised or I'm not understanding your full option here?

                  beverly

                  1 of 1 people found this helpful
                  • 6. Re: Combine value lists, all combinations
                    Henry_2

                    beverly wrote:

                     

                    OPTION1

                    SELECT table1.field1, table2.field2, table3.field3, table1.field1 + '-' +table2.field2 +'-' +table3.field3

                    possibly the "+" is trying to add the values, so use "|" instead:

                    SELECT table1.field1, table2.field2, table3.field3, table1.field1|'-'|table2.field2|'-'|table3.field3 AS compoundKey

                    Super, the query now produces the correct output (after changing the piple above into a double pipe ||)

                     

                    So now I have a .tab file (or variable) with this:

                    col1  col2   col3    col4(compound)

                    A     Z1     001     A-Z1-001

                    A     Z1     002     A-Z1-002

                    A     Z2     001     A-Z2-001

                     

                    ...all combinations of col1,2,3.

                     

                    And want to add the new combinations to the existing table (product attribute values), which looks like this:

                    col1  col2   col3    col4(compound)     col5

                    A     Z1     001     A-Z1-001           some important value

                    A     Z1     002     A-Z1-002           some important value

                    A     Z2     001     A-Z2-001           some important value

                     

                    (col1 could be a store id, col2 a product attribute id, col3 a product id, and col5 the value

                    for example: for store ENG, attribute Size, product Shirt,  size L

                     

                    Still puzzled how to avoid importing the records for compound keys that already exist in the product attribute value table. Any idea's?

                    • 7. Re: Combine value lists, all combinations
                      beverly

                      Yes! ||

                       

                      Import with Matching?

                       

                      Sent from miPhone

                      • 8. Re: Combine value lists, all combinations
                        Henry_2

                        Import with Matching would update existing values as far as I know, goals is only creation of new records (for later data entry). Matching would also overwrite existing values as well in col5 (because upon inital creation i also add a default value in col5. That default value could later change.

                         

                        I did as follows, first query the existing table:

                        Let (

                          [

                          $query = "

                               SELECT \"compoundKey\"

                               FROM  \"product_attribute_value\" "

                          ; $compoundKeyList = ExecuteSQL ( $query ; "" ; "" )

                          ; $compoundKeyArray = "'" & Substitute ( $compoundKeyList ; ¶ ; "','" ) & "'"

                          ]; $result = ""

                        )

                        ]

                        This produces: 'A-Z1-001','A-Z1-002','A-Z1-003'

                         

                        And then adjusted the query discussed earlier to something like this:

                        query:

                        SELECT table1.field1, table1.field2, table1.field3, table2.field2, table3.field3, table1.field1||'-'||table2.field2||'-'||table3.field3 AS compoundKey

                        FROM  \"table1\", \"table2\", \"table3\"

                        WHERE table1.field6 = 1

                        AND compoundKey NOT IN (" & $compoundKeyArray & ") "

                         

                        This query however produces a '?'.... have not found my mistake, perhaps the temporary column name 'compoundKey' cannot be used this way? Or needs a table prefix, if so, which how to add a temporary table name in this case?

                        • 9. Re: Combine value lists, all combinations
                          beverly

                          you might "update" match on something that would be one record with a bogus key or something. Then the rest of the records would add. I'm talking out of my hat at the end of the day, so fuzzy on the details of what would work or not.

                           

                          the IN can be problematic, add 'NOT' to that and well, I wouldn't try. You are trying to 'create' records based on existing records and this SQL is not up to the challenge.

                           

                          However, since your eSQL query result is in a text field (or variable) before you export/import, I might be tempted to omit lines where the key is already in your set. Loop through with GetValue(), since it's a return delimited set.

                           

                          Again, without seeing what all you are doing and have to do, these are just mutterings of a crazy lady.

                          • 10. Re: Combine value lists, all combinations
                            Henry_2

                            ok I might get the bogus key for rejecting existing records on import, although also my end of day:)

                             

                            The NOT IN... I made a change:

                            AND table1.field1||'-'||table2.field2||'-'||table3.field3 NOT IN (" & $compoundKeyArray & ") "

                             

                            Now it is running, already for an hour or so... so you are probably right, it's not up for the challenge so it seems.

                             

                            Regarding looping the lines of the eSQL result with GetValue(), that sounds like a good solution to try. Would that look something like this?

                            1. create the variable with eSQL result
                            2. load the existing compoundKeys into a variable
                            3. start looping eSQL result lines. And in each iteration:
                              1. first get the compoundKey from the current line,
                              2. patterncount that compoundKey against the whole list of existing compound keys
                              3. if patterncount = 0 append the current line into a $newLines variable
                            4. export the newLines
                            5. import the newLines.tab (add records)

                            Or do you see some inefficiency here?

                            • 11. Re: Combine value lists, all combinations
                              beverly

                              If you do not want to 'pre-import' and create a relationship to see what is not already there, then I believe you are scripting as I was thinking.

                              Have a good evening!

                              • 12. Re: Combine value lists, all combinations
                                user19752

                                (Even learned that adding some other fields of table1,2,3, worked as needed (meaning adding additional fields of used tables did not result in additional combinations). Also added CASE and WHEN clauses to get exactly the output I needed)

                                 

                                If you need combination of fields in a table, you can use alias for the table like as

                                 

                                SELECT t1.f1, t2.f2

                                FROM table t1, table t2

                                WHERE <<some conditions, especially exclude NULL values>>

                                 

                                (same table used 2 times in FROM with different alias name)

                                1 of 1 people found this helpful
                                • 13. Re: Combine value lists, all combinations
                                  beverly

                                  Especially helpful for Self-joins.

                                  Thanks, user19752

                                  • 14. Re: Combine value lists, all combinations
                                    Henry_2

                                    thanks, good to know!

                                    1 2 Previous Next