8 Replies Latest reply: Jun 7, 2010 11:36 AM by HofbauerAn RSS

    Match to second field, if first field is empty?

    HofbauerAn

      Hi,

      I have a table, which have two key values, which are connected to the other tables in the docuement (Sales Area, Subsidiarys).
      (i.e.) i have a row, which looks like the following:

      ID Name Sales Area Subsidiary

      1 Bill Paxton Germany Munich
      2 Jake Newton England

       

      When I'm selecting ID 1 QlikView matches the data related to "Munich" - so QlikView first match the Subsidiary column.
      Now I want to select ID 2. Here, there's no entry in Subsidiary, so QlikView should match the data from "England" - the Sales Area column.
      But when I'm selecting ID 2, QlikView doesn't show up any data.

      I added the part of the picture from the table viewer, which shows, that the two columns of the table are connected to the other tables correctly.

      Any ideas how to match on Sales Area, if no entry in Subsidiary exists?

      Thanks for help!

      error loading image

        • Match to second field, if first field is empty?
          HofbauerAn

          No Ideas?
          I really need help with this.

          Thanks.

          • Match to second field, if first field is empty?
            joseph.thoppil

            Hi Hofbauren,

            Sometimes if there are synthetic keys in your table then the data may not match with each other or you wont be able to get the correct results you are looking for. so what you have to do is remove the synthetic key first. Even if this is changed, you wont be getting a solution for the problem you have specified here. What I suggest is that, if you have a mapping table to map different countries and its corresponding subsidiaries, you will get a solution. You can just add an inline table having countries and its coerresponding subsidiaries and then look for a solution. hope this helps you.....

            Thanks Joseph..........

              • Match to second field, if first field is empty?
                HofbauerAn

                Hi Thopz,

                thanks for your answer.
                Can you please explain your suggestions of how I can solve my problem a little further.
                Would be great, because I'm not really expirienced with QlikView.


                Thank you very much.

                  • Match to second field, if first field is empty?
                    joseph.thoppil

                    Hi HofbauerAn,

                    You want me to explain the solution part right. OK... What I said is, if you have a table like this ie.

                    load * inline

                    [

                    Countries, Subsidiary1

                    Germany, Munich

                    Germany, XYZ

                    England, Manchester

                    ];

                    This is a mapping table with your original table and the key field here is the Country field. Then instead of using Subsidary in you chart use the presently created Subsidiary1 so that whenever a country is seleceted or country is given in the dimension, it gets its mpping subsidiary location from the inline table we have created. I hope what I am trying to say is clear.

                    Regarding the synthetic keys, please go through in the reference manual. It is explained well there on what synthetic keys are and how to solve the synthetic keys.

                    Hope this helps you. If you want any more help please ask.

                    Thanks Joseph......

                      • Match to second field, if first field is empty?
                        HofbauerAn

                        Hi Thopz,

                        thanks for your help.
                        Ok, i need to create an "Inline Table" .

                        So in my situation the table looks the following:

                        LOAD * INLINE
                        [
                        Sales Area, Subsidiary1 // I dont know exactly for what the 1 have to be here
                        CISMAT, CIS
                        CISMAT, ME
                        Southern Europe, TIO
                        Central Europe, TSA
                        Central Europe, TGA
                        ]
                        FROM TABLE 1;

                        So, when I'm understanding right, with an Inline table I can relate each Subsidiary to the right Sales Area and I have to write each relation for each Subsidiary.

                        What is the exactly syntax for the INLINE Load. I get an error, when trying to load it in the way explained above.

                        Maybe I'm on the totally wrong way...

                        Thank you very much for your help in this case.

                         

                          • Match to second field, if first field is empty?
                            joseph.thoppil

                            Hi,

                            what you have written is correct except the last line. you dont have to specify the from clause here. just fter the cloasing bracket ] put a semicolon to end it as shown in the code given below.Inline tables are not derived from any tables. they are eindividual tables of its own. so the exact syntax is like this

                            load * inline

                            [

                            country, subsidiary

                            india, xyz

                            england, abc

                            ];

                            Once you create this, an automatic link will be vreated by qlikview with the countries field in your original table. The thing is you have to give the same name tht is 'country' in the mapping table as it is in your original table.Hope this helps you!!

                            Thanks Joseph.........