7 Replies Latest reply: Sep 5, 2013 6:54 AM by Friedrich Hofmann RSS

    Basic stripting question

    Friedrich Hofmann

      Hi,

       

      this is seemingly a very trivial question - I just come across this for the first time.

      In one Dashboard that I had finished about halfway, I now have to integrate data from a new source: An exact mirror_image of the database for another plant.

      - To later be able to select one plant or the other (or both), I have included a plant_key into the tables.

      - I have given this different names in the tables so that I don't end up with a lot of synthetic keys.

      - Now, when I use the same fields I have in the "old" version, the keys are not unique anymore.

      <=> Associating tables where the key is not unique in either table is never a good idea in my experience.

      => So, do I have to rename the existing keyfields (so they are not associated anymore) and build new ones, including the plant_key (that's the only thing I can think of as a reason)? Won't that influence the possibility of later selecting plants?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Basic stripting question
          Stefan Wühl

          Not sure if I can follow.

           

          If I assume correctly what you are trying to achieve, I would Load both tables into a concatenated table, adding a field Source to describe the source of the data, and maybe force selection of one source in the Dashboard.

          • Re: Basic stripting question
            Jared Papador

            If the two databases are the same you could concatenate the tables.  If you'd still like to select the different plants on the primary key of the tables you could create a key that distinguishes the two:

             

            Table1:

            'PlantA' & PrimaryKeyField

            Field1,

            Field2,

            etc

             

            Concatenate (Table1)

            LOAD

            'PlantB' & PrimaryKeyField,

            Field1,

            Field2,

            etc

             

            This way you would still have one field for each dimension instead of an old and a new and a way to distinguish the two plants.  You could even take it a step further and create a plant field:

             

            If(Left(PrimaryKeyField, 5) = 'PlantA', 'Plant A', 'Plant B') as Plant

              • Re: Basic stripting question
                Friedrich Hofmann

                Hi jpapador,

                 

                I see I omitted a few things.

                - I do concatenate the tables - as the structure must be the same, why create two tables.

                <=> The problem is slightly different:

                - Apparently, some IDs repeat in the other plant

                => That is why after adding the new data, the keys are not unique anymore.

                By creating a compound key including the plant_key and renaming the former keyfields (to get them out of the way and avoid synthetic keys), one of my tables (I have three tables in my data_model currently) is back to a "primary key". That is unique if I remember correctly, so that's all right.

                <=> In the other table I have to connect, the key is not unique anymore ("key", was "Perfect key") and creating a compound key incl. the plant does not help matters here.

                 

                Thanks a lot!

                Best regards,

                 

                DataNibbler

              • Re: Basic stripting question
                Kourosh Karimi-Ghovanlou

                If I'm understanding you correctly, I often have similar issues. All I would do is keep the same field names in both sets and use a new field (e.g. plant_key) which is unique to each one. So you could have:

                Field 1, Field 2, plant_key

                xxx, xxxx, Plant_1

                xxx, xxxx, Plant_2


                Let QlikView concatenate the two tables and use plant_key to differentiate between the sets.

                  • Re: Basic stripting question
                    Jared Papador

                    That is correct.  To simplify is you could do

                    Plant A as Plant_Key in the first table and

                    Plant B as Plant_Key in the second table.  That way both Tables will have the same field names while concatenating and you can also use the Plant_Key field to distinguish between the two.

                      • Re: Basic stripting question
                        Friedrich Hofmann

                        Hi jpapador,

                         

                        that's what I'm doing.

                        What's worrying me is the fact that I cannot create a unique key anymore - strangely, in the old version of the Dashboard, it works - after a reload, the keys are still unique, so the base data can't have changed - so the error must lie with what I have done.

                          • Re: Basic stripting question
                            Friedrich Hofmann

                            OK,

                             

                            this is solved I guess.

                            I just had a problem concerning the quality of the keys (Perfect key/ Primary key/ Key). As I thought, I had renamed something and some formulas were not working anymore.

                            A bit of logical thinking and retracing my steps solved the problem.

                            Now the quality of the keys is what it was before.

                             

                            Thanks anyway!

                            Best regards,

                             

                            DataNibbler