7 Replies Latest reply: Mar 4, 2016 7:42 PM by John Witherspoon RSS

    Beginner - how to add a new field to a report

    Todd Bransky

      I am a beginner to Qlik and am likely asking a very basic question here so, my apologies. 

       

      I would like to add a new field named, "Brand" to several of our charts and/or reports in Qlik.  "Brand " is a grouping of "Products".  I already have the "Products" dimension available to my reports, but I need a field named "Brand" to be added/displayed.  For each value of product returned in a report, I would also like to display the value of "Brand". 

       

      The list of products will be small (less than 30) and will rarely change if that makes any difference on how I can (or should) accomplish this.  With my limited knowledge-experience in Qlik, please respond as if you are talking to a 5th grader :-)   Thanks so much!

       

      ProductBrand
      Product 1Brand 1
      Product 2Brand 1
      Product 3Brand 1
      Product 4Brand 1
      Product 5Brand 2
      Product 6Brand 2
      Product 7Brand 2
        • Re: Beginner - how to add a new field to a report
          John Witherspoon

          Simplest, at least to describe, might be an inline load of the brand data.

           

          [Brands]:
          LOAD * INLINE [
          Brand, Product
          Brand 1, Product 1
          Brand 1, Product 2
          Brand 1, Product 3
          Brand 1, Product 4
          Brand 2, Product 5
          Brand 2, Product 6
          Brand 2, Product 7
          ];

           

          Then just put Brand in your chart, and QlikView will handle the rest.

           

          It might be better to load this data from an Excel file, say, so that you don't need to reinstall your application if the data changes. But I do things like the above fairly frequently. Often, the structure of what I'm trying to do changes as often as the data, and at that point, having both an excel file and a QlikView application to install just complicates things rather than simplifies things.


          I do tend to write the script slightly differently, though, for this type of inline load.

           

          [Brands]:
          LOAD
          Brand
          ,subfield(Products,', ') as Product
          INLINE [
          Brand: Products
          Brand 1: Product 1, Product 2, Product 3, Product 4
          Brand 2: Product 5, Product 6, Product 7
          ] (delimiter is ':');

            • Re: Beginner - how to add a new field to a report
              John Witherspoon

              It looks like I did some performance testing back in 2010, and determined that if the number of mapping tables is small, using a left join goes faster than applying a map. Performance testing from 2010 is of course not necessarily relevant to the product in 2016. There are other differences between the two approaches beyond just performance, like you can supply a default value with a map, and a map will never accidentally duplicate rows if you mess up your table, where the left join will.

               

              In any case, here are those two approaches for comparison to my previous post. I've duplicated the entry for Product 4 and removed the entry for product 7 to show the difference in behavior. In this case, the left join gets the wrong sales for product 4, and shows null instead of unknown for product 7. The map is more tolerant of mistakes like these. Finally, I've modified the original approach to show the difference between it and both of these in its handling of the bad data. It still has the right sales for product 4, but shows null for product 7.

              • Re: Beginner - how to add a new field to a report
                Todd Bransky

                Thank you for the reply!  I will look at each of these options and see which one(s) will work best for us. 

              • Re: Beginner - how to add a new field to a report
                Todd Bransky

                Thanks for the help guys (baby-steps) and now I have a follow-up question....

                 

                I was able to get the new table created using a load script from a spreadsheet:

                 

                ProductBrand:

                LOAD PRODUCTNAME,

                     BRAND

                FROM

                [C:\Analytix\Include\Spreadsheets\ProductBrand.xls]

                (biff, embedded labels, table is Product_Brand$);

                 

                Now, where do I go to join that new table (ProductBrand) to an existing table (Product) where Product.Productname = ProductBrand.ProductName.  I want to have a 1:1 relationship between those two tables but it will need to outer-join to the ProductBrand table in the event new ProductNames are a added to the Products table and we have not yet updated the ProductBrand table.

                 

                I hope I am asking that question properly.  My goal would be to pull "Brand" and "ProductName" into my tables/charts.  Right now if I try to pull Brand into the table/chart, I duplicate data because there is no join to the ProductBrand table.

                 

                Thanks again for your help!

                  • Re: Beginner - how to add a new field to a report
                    John Witherspoon

                    I'm not sure if I'm quite understanding the question. If you want to join the tables in script, instead of loading the data into ProductBrand, just load Product first, and join the brand data onto Product.

                     

                    LEFT JOIN ([Product])
                    LOAD PRODUCTNAME,BRAND
                    FROM [C:\Analytix\Include\Spreadsheets\ProductBrand.xls]
                    (biff, embedded labels, table is Product_Brand$);

                     

                    I'm saying left instead of outer because I wouldn't think you'd want to see products that are not yet on the product table, only ones that aren't yet on the product brand table. If all you are doing is adding that one field, BRAND, then that's probably what I'd do.

                     

                    If there are actually a bunch of fields, and possibly rather than outer join, I'd just load the two tables separately. As long as they have the same field name for product name, QlikView will understand the relationship between the two tables and take care of anything OK. If you're duplicating data with them as separate tables, them I'm guessing you aren't using the exact same field name (spaces, capitalization, etc.). You'd need to fix that to do a join as well. Another source of duplication would be if a product name could be associated with more than one brand. If so, a join will duplicate rows explicitly, and leaving the tables separate should be better behaved, or perhaps you want to select some sort of "primary brand" during the load as the only one you care about.