8 Replies Latest reply: Jan 5, 2017 7:06 PM by Christopher Obert RSS

    Qlik Data market data causing issues

    Christopher Obert

      HI all, thanks in advance for your help.

       

      Here is my problem, my company has purchased Qlik and I am tasked with learning it to teach our end users. I am going through the Qlik provided activity book to learn how Qlik works. (April 2016 Release, Qlik v2.2  book of Qlik Modeling for Qlik Sense). I am having some difficulty because the described behavior in the book is not matching the observed behavior of the tool. This is due to in part, I think, that we are using v3.0 instead of v2.2 (if anyone knows of v3.0 learning material that would be AWESOME).  Currently I am trying to work through the data market section. (pg. 194) .

       

      The described behavior is that I get data from the market free section, and dowload country population data.

          "6. On the Select data to load step, select the following

                - Country: Country - this should select every country in the list

                - Sex: Total

                -Age Group: Total

                - Year: Most Recent

          7. Click load data and finish

          8. Click close

          9. Open the Data manager from the Navigation menu. Notice that only the World Population by Country table has edit and delete icons when you hover over.

      ...

          11. Preview the new table and check the field values. Country and Persons are the only two fields you need to keep...."

       

      This indicates that at this point of the manual that only one table has been downloaded. In fact 4 tables have loaded, one for each selection with FK in the World Population by Country pointing to the Country, Sex, and Age Group tables.

      Fine, something has changed from printing to today, so here is my problem. I want to convert these 4 tables to one table, specifically I want one table with Country and Persons (per the manual instructions for this activity). The problem is that EVERY TIME I try to join Country and World Population by Country the resulting table is a Cartesian product. The Country table and the WPbC table both have 228 rows of data I have triple (x1000) checked the column names, qlik data modeler recognizes the connection between the two tables (column: _dim_8q8e) , if I leave everything "as i"s and create a sheet in the App Overview and check the results by dropping country and persons into a table, everything is fine. I get 228 rows of data. When I copy these tables into temp tables I get 228 rows apiece,  But as soon as I try to join country into the WPbC table or Persons into the Country temp tables I get 228x228 rows of data. Here is my code :

      WorldPopTemp:

      Load

        Persons as WpPersons,

          _dim_8q8e AS countryID

      RESIDENT [World population by country];

      /*INNER JOIN

      LOAD

      Country as wpCountry

      RESIDENT Country;

      */

      CountryTemp:

      LOAD

      _dim_8q8e as countryID,

      Country //as Con_con

      RESIDENT Country;

       

      //Here is new table

      WorldPop:

      LOAD DISTINCT

      WpPersons as Population,

      countryID as CID

      RESIDENT WorldPopTemp;

      //RESIDENT [World population by country];

      INNER JOIN (WorldPop)

      LOAD

      Country AS CT_Country

      RESIDENT CountryTemp;

      //RESIDENT Country ;

       

       

      Does anybody see why this results in Cartesian product?

       

      Thank you

        • Re: Qlik Data market data causing issues
          Michael Tarallo

          Hi Christopher - let me see if I can get some help for you on this. You are correct, Qlik DataMarket has changed a bit since the writing of that manual - is this one of our provided materials or a 3rd party?

           

          hgs - can you take a look at this and see if you can point Chris in the right direction.

           

          BTW - you can see video assets to help you learn and get started here: New to Qlik Sense Videos

           

           

          When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

           

          Regards,

           

          Michael Tarallo (@mtarallo) | Twitter

          Qlik

            • Re: Qlik Data market data causing issues
              Christopher Obert

              So here is an update, we had a QLIK consultant come in today (they were literally sitting with me when your reply posted) for some other questions and he agreed to take a look for me.  The issues was that I was not pulling the shared column (i.e. the column that connects two tables) in the join load script, I was relying on QLIK "knowing" the connection.  This requirement was not called out in the join section of the manual. The manual just always listed all the columns all the time in both tables in the examples, because they always just included all the columns (and never called out that the shared column was there to create the link) I sort of assumed it was like the automatic concatenation feature (i.e. the reason you need to explicitly state 'No concatenate' when loading a second table with identically named columns as another table to prevent QLIK from just "knowing" to concatenate it to the original table)  and QLIK was supposed to just know that it was there. Bit of a bonehead move on my part I guess, but understandable when viewed in the light that QLIK just detects these connections everywhere else automatically, (like in the sheets/visualizations) .

               

              The correct script on the join is (for WorldTemp join)

              WorldPopTemp:

              Load

                Persons as WpPersons,

                  _dim_8q8e

              RESIDENT [World population by country];

              JOIN

              LOAD

              Country as wpCountry,

                _dim_8q8e

              RESIDENT Country;

               

              Now I can create the mapping table after creating this table.

               

              Pop_Map:

              MAPPING LOAD

                countryID,

                  WpPersons

              RESIDENT  WorldPopTemp;

               

              //DROP unneeded tables here

              DROP TABLE [World population by country];

              DROP TABLE Country;

              DROP TABLE WorldPopTemp;

               

              Basically the manual is unclear at this point of the instruction. The activity in the learning manual implies that Qlik datamart only creates one table in the model, when it in fact pulls 4 tables.  Thus there are no instructions on transforming the four tables into one table.  And therefore, the next step in the instruction (i.e. creating the mapping table) fails.

               

              Thanks for the reply.

                • Re: Qlik Data market data causing issues
                  Michael Tarallo

                  Awesome to hear - can you please provide me with the link or screenshot of the learning manual so we can have the appropriate parties update the content?

                   

                  Thanks again.

                   

                   

                  When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

                   

                  Regards,

                   

                  Mike Tarallo (@mtarallo) | https://twitter.com/mtarallo

                  Qlik

              • Re: Qlik Data market data causing issues
                Hjalmar Gislason

                Hello Christopher,

                 

                Thanks for taking the time to report this. I want to make sure I understand what you are trying to achieve here. As you have realized, what has changed since the writing is that Qlik DataMarket now returns a data model that consists of several tables, rather than a single table. The model is already associated in the right way, and as long as you didn't select additional fields that you don't need any more, all of the fields are required to keep the data model together. So, is there anything preventing you from going ahead with the project if you simply load the data model and keep it the way it is?

                 

                (We are working on hiding the "system" fields that are there simply to join the tables to keep the field list clean, but that's for later)

                  • Re: Qlik Data market data causing issues
                    Christopher Obert

                    The only issue was that the lesson assumed one table was created and gave instructions with this in mind. I needed to combine the tables together to finish the lesson. The rest of the model used a column from one qlikmart table as a foreign key, but then used another column from a different qlikmart table for the information. The lesson was designed to have these in one table and it just did not work with having the columns in separate tables.