8 Replies Latest reply: Sep 8, 2011 1:21 PM by John Witherspoon RSS

    Combining two fields from the same table to create a third unique field

      Hi I was wondering how to best combine two fields in an existing data model table so that a third unique Field is created.

       

      Eg.

       

      Table name: fact

       

      Field1: [Company Code]

      Field2: [Profit Centre]

      Field3: PCmap

       

       

      I thought that I would do it like so:

       

      LOAD

      [Company Code]&[Profit Centre] as PCmap,

      Resident fact;

       

      But this doesn't work...

       

      I have only been using QV for 3 weeks so still trying to get my head around the complexities..

       

      Thank you in advance

        • Combining two fields from the same table to create a third unique field
          Stefan Wühl

          Hi,

           

          your code will create a separate table, unlinked to the fact table. Before getting into deep, why not just adding the above line

           

          [Company Code]&[Profit Centre] as PCmap,

          to you original fact table load (where you also load [Company Code] and [Profit Centre]?

           

          Then, you might want to add a seperator between the both parts (more for human readers than anything else):

           

          [Company Code] & '-' & [Profit Centre] as PCmap,

           

          But more performant would probably be a numeric type key, maybe using  autonumber or

          Autonumberhash256 ( [Company Code],[Profit Centre]) as PCmap,

           

          Hope that helps,

          Stefan

           

            • Combining two fields from the same table to create a third unique field

              Thank you for the prompt response.

               

              unfortunately the table in question is in a massive data model which I am loading in at the begining of my app.

               

              given this what would be the next best course of action?

                • Combining two fields from the same table to create a third unique field
                  John Witherspoon

                  Are you loading the massive data model from QVD?  If so, best would be to add this logic when the QVD is created so that you can keep an optimized load in the user application(s).  If not, then why couldn't you add the logic to the original load?  I'm not understanding why having a massive data model is making you conclude that you shouldn't establish this additional field during that load.

                    • Combining two fields from the same table to create a third unique field

                      Apologies. I just thought that it was not possible to add the field during the Data model load.

                       

                      I think I may need a little more training on the basics...

                        • Re: Combining two fields from the same table to create a third unique field
                          John Witherspoon

                          Ah, OK.  Are you currently loading from a database?  Does your load look something like this?

                           

                          fact:
                          SELECT
                          [Company Code]
                          ,[Profit Centre]
                          FROM something
                          WHERE whatever
                          ;

                           

                          You can add the new field with a preceding load.  Basically, the load statement is implied when you do a select.  The above is functionally equivalent to the below, and I suspect is processed exactly the same internally:

                           

                          fact:
                          LOAD *
                          ;
                          SELECT
                          [Company Code]
                          ,[Profit Centre]
                          FROM something
                          WHERE whatever
                          ;

                           

                          And that load statement then gives you a place to stick your new field:

                           

                          fact:
                          LOAD *
                          ,[Company Code] & ' - ' & [Profit Centre] as PCmap
                          ;
                          SELECT
                          [Company Code]
                          ,[Profit Centre]
                          FROM something
                          WHERE whatever
                          ;

                           

                          Of course you might be loading from Excel or who knows what, but the general idea tends to be the same.  There ARE exceptions, like when loading from a QVD like I already mentioned.  You CAN add exactly the same thing to a QVD load, but it can be inefficient from a performance standpoint, so another approach is generally called for with a large data model like yours.  You could also have issues if you were doing a binary load, though we'd solve it about like we would the QVD load, by inserting the new field in the source of the binary load. 

                           

                          If you gave us specifics, we could probably give you the solution, but I'm guessing that you have enough at this point to experiment and solve it on your own.  Do monitor load times.  If your load time increases by more than a negligible amount, then probably something is wrong, even if you get the right results in the end.

                            • Combining two fields from the same table to create a third unique field

                              Hi Thanks for this...

                               

                              I am actually doing a binary load which means I only see a single line in the load script. (this confuses the nube in me)

                               

                              Since last night my app sponsor has decided that he wants something else from a second table applied too.. I have decided to run with the creation of a third table and map it in from there.

                               

                              Thank you all for your support!

                                • Re: Combining two fields from the same table to create a third unique field
                                  John Witherspoon

                                  OK, with a binary load, you are correct that you cannot add another field to that specific load.

                                   

                                  I think the RIGHT solution is to put the new field in the load inside of the QVW that you are doing the binary load from.  Now we're back to the other cases I was talking about.  And if that QVW is loading from a QVD, then I think the RIGHT solution is to establish the field in the QVW that is building the QVD.

                                   

                                  Whatever QVW it's in, I think the right place to establish the field is in the initial load of the rest of the data in that table.

                                   

                                  "Right" here is open to interpretation, though.  Perhaps what I should say is something more like, "You will likely get the best overall performance from your system if you put the new field in the initial load of the rest of the data in that table."

                                   

                                  But if you can't talk your team into fixing the right QVW ("some guy on a forum said we should" isn't usually a very convincing argument in a professional environment), then you're stuck doing something with lower performance and additional complexity.  In that case, I'd probably go with the left join that Stefan mentioned, which I think is what you ended up doing?  Honestly, that's fine.  It's not what I would do, but it's fine.

                          • Combining two fields from the same table to create a third unique field
                            Stefan Wühl

                            Well, you would have to reload anyway, so I see no real advantage at the moment, but maybe:

                             

                            left join (fact) LOAD

                            [Company Code],

                            [Profit Centre],

                            [Company Code] &'-'& [Profit Centre] as PCmap

                            Resident fact;