11 Replies Latest reply: Nov 10, 2016 12:52 PM by Evgenii Senko RSS

    Many to one relationship

    Evgenii Senko

      Hi,

       

      Need help, this is urgent!

       

      I have to tables.

      Have to be relationship between them.

      Code1 - AllCodes

      Code2 - AllCodes

      Code3 - AllCodes

       

      After update if select Amigo will be showed "Store1" according to Code1.

      If select "Mall" will be showed Store1 and Store2 according to Code1 and Code3.

       

      Codes: 

      Code1Code2Code3Store
      112233Store1
      W4S2V7Store2

       

      Info:

      AllCodesTypeSquareName
      11Street100Amigo
      W4Mall200Jump
      33Mall300Free

       

      Thanks in advance!

        • Re: Many to one relationship
          Fer Fer

          Hi,

          1-example

          TMP:
          LOAD * Inline
          [
          Code1,     Code2,     Code3,     Store
          11,  22,  33,  Store1
          W4,  S2,  V7,  Store2
          ]
          ;

          CODES:
          LOAD Code1 AS AllCodes, Store Resident TMP;
          LOAD Code2 AS AllCodes, Store Resident TMP;
          LOAD Code3 AS AllCodes, Store Resident TMP;

          Left Join

           


          INFO:
          LOAD * Inline
          [
          AllCodes,Type,Square,Name
          11,  Street,    100,Amigo
          W4,  Mall, 200,Jump
          33,  Mall, 300,Free
          ]
          ;

          DROP Table TMP;

            • Re: Many to one relationship
              Evgenii Senko

              Hi fer fer,

               

              Thank you for reply.

               

              In real picture I have more then three rows in table.

              They are thouthands.

               

              What is code should be in these parts ?

               

              TMP:
              LOAD * Inline
              [
              Code1,     Code2,     Code3,     Store
              11,  22,  33,  Store1
              W4,  S2,  V7,  Store2
              ]
              ;



              INFO:
              LOAD * Inline
              [
              AllCodes,Type,Square,Name
              11,  Street,    100,Amigo
              W4,  Mall, 200,Jump
              33,  Mall, 300,Free
              ]
              ;

                • Re: Many to one relationship
                  John Witherspoon

                  Maybe I shouldn't reply for him, but his example does the same as mine does, converts columns into rows. In that sense, it's the "same" solution. Like my example, it doesn't matter how many rows you have in either table. It should still work fine. And replace any inline load with whatever your real data source is. I suspect that crosstable is a more efficient way to convert the columns into rows, though. That's its whole job. I've done no performance testing, though.

              • Re: Many to one relationship
                John Witherspoon

                I'd use a crosstable load. See attached and below.

                 

                Codes:
                CROSSTABLE (Codenum,Code)
                LOAD * INLINE [
                Store,Code1,Code2,Code3
                Store1,11,22,33
                Store2,W4,S2,V7
                ];

                Data:
                LOAD * INLINE [
                Code,Type,Square,Name
                11,Street,100,Amigo
                W4,Mall,200,Jump
                33,Mall,300,Free
                ];

                DROP FIELD Codenum;

                Capture.PNG

                Capture2.PNG

                  • Re: Many to one relationship
                    Evgenii Senko

                    John,

                     

                    Thanks for the example.

                     

                    Please help to understand what should be the code if I have a lot of rows of data in these tables?

                    I can't enter it manually.

                    It shoud be downloaded by script.

                      • Re: Many to one relationship
                        John Witherspoon

                        We use inline loads with only a few rows when giving examples because that's a very clear way to show example data, and not have to know whether you're loading using SQL, from a QVD, from an Excel file, or whatever. Replace what we load inline with whatever your actual data source is. Those details are usually beside the point, not really relevant to solving the specific question.

                         

                        In this case, it doesn't matter how many rows you have in the tables. The crosstable is sensitive to the column names and positions, so you'd have to be careful when loading your real table to have things in the right order. You might even want to split the first table into two tables linked by an ID. But it's hard for me to know without knowing your actual data. And it's hard for me to give you exact script without knowing your data sources as well. But assuming you have a lot more fields in both tables, and you're loading from QVDs, it might look something like this, including my split of your first table into two, only "necessary" if you have additional fields in that first table:

                         

                        StoreData:
                        LOAD
                        StoreID
                        ,StoreName
                        ,some
                        ,other
                        ,fields
                        FROM Store.qvd (QVD)
                        ;
                        StoreCodes:
                        CROSSTABLE (Codenum,Code)
                        LOAD
                        StoreID
                        ,Code1
                        ,Code2
                        ,Code3
                        ,Code4
                        ,etc. for all codes
                        FROM Store.qvd (QVD)
                        ;
                        CodeData:
                        LOAD
                        Code
                        ,Type
                        ,Square
                        ,Name
                        ,Something
                        ,SomethingElse
                        ,AndSoOn
                        FROM Codes.qvd (QVD)
                        ;

                         

                        But even that's not what I'd really do, because in the real world I'd be breaking apart the data before even storing in QVDs, not breaking it apart loading from QVDs. But I hope I don't have to expand this example even further to get the idea across. In any case, the important insight is to use crosstable to restructure the code portion of the data from the first table, converting columns into rows. This load is not sensitive to how many rows and columns you have, any more than any other load is sensitive to how many rows and columns you have.

                          • Re: Many to one relationship
                            Evgenii Senko

                            John,

                             

                            According to your explanation I understood how CrossTable function works.

                             

                            But my mistake I didn't show all structure of tables.

                            I was trying to implement this solution for my task but found an issue.

                             

                            My table includes other columns which I have to use also.

                             

                            Codes: 

                            Code1Code2Code3StoreSalesQtyMarginetc
                            112233Store115001000000etc1
                            W4S2V7Store22000200000etc2

                             

                            Info:

                            AllCodesTypeSquareName
                            11Street100Amigo
                            W4Mall200Jump
                            33Mall300Free

                             

                            CrossTable convert all columns in one.

                             

                            How can I see also such metrics as SalesQty, Margin, etc if use CrossTable relationship?