4 Replies Latest reply: Jun 3, 2011 1:35 PM by John Witherspoon RSS

    How do you union 2 qlikview tables

    Robert Young

      Hi Everyone,

       

      Is there a way to union 2 tables when creating a chart.  I currently have one table with a series of categories and another with a different series.  I need to create a chart that will have a union of these tables.  I know I could create a new table during the load but am trying to avoid this due to size of the current app and how much data I would be duplicating.  Below is an example of the 2 different tables and what I am trying to archive.  Please note that the structure has been simplified to demonstrate my example.

       

      Table A

      Project     Area     Product     Category     Time

      AAA         A        AAAAA       A            10

      AAA         A        AAAAA       C            7

      AAA         A        BBBBB       A            3

      AAA         A        AAAAA       B            8

      AAA         A        CCCCC       C            10

      AAA         B        BBBBB       A            10

      AAA         B        BBBBB       C            17

      AAA         B        CCCCC       A            10

      AAA         B        CCCCC       B            24

      AAA         B        DDDDD       C            15

       

      Table B - This is category W

      Project     Area     Time

      AAA         A        20

      AAA         B        32

      AAA         B        19

       

      Chart would look like

      Project     Area     Category     Time

      AAA         A        A            13

      AAA         A        B            8

      AAA         A        C            17

      AAA         A        W            20

      AAA         B        A            20

      AAA         B        B            24

      AAA         B        C            32

      AAA         B        W            51

       

      Any suggestions other than during the load.

        • How do you union 2 qlikview tables
          John Witherspoon

          If you were to concatenate Table B onto Table A while adding a Category field with value W for the Table B data, where is the duplication?  Also, even when there is duplication or denormalization, this isn't typically a problem for QlikView due to its compression.

          • Re: How do you union 2 qlikview tables

            Hi, see this example.

            • How do you union 2 qlikview tables

              Hi Robert,

               

              If you had the following load statements:

               

              TableA:

              load * inline

              [

              Project,Area,Product,Category,Time

              AAA,    A,   AAAAA,  A,  10

              AAA,    A,   AAAAA,  C,  7

              AAA,    A,   BBBBB,  A,  3

              AAA,    A,   AAAAA,  B,  8

              AAA,    A,   CCCCC,  C,  10

              AAA,    B,   BBBBB,  A,  10

              AAA,    B,   BBBBB,  C,  17

              AAA,    B,   CCCCC,  A,  10

              AAA,    B,   CCCCC,  B,  24

              AAA,    B,   DDDDD,  C,  15

              ];

               

               

              TableB:

              load * inline

              [

              Project,Area,Time

              AAA,    A,   20

              AAA,    B,   32

              AAA,    B,   19

              ];

               

               

               

              You could build a straight table with the following dimensions:

              • Project
              • Area
              • =if(isnull(Category), 'W', Category) calculated dimension

               

              Expression:

              • sum([Time])

               

               

              That should give you the desired result without having to perform a reloading.

                • How do you union 2 qlikview tables
                  John Witherspoon

                  Ecorrea, we'd only want your Table_Result table if we really ARE trying to get rid of the Product dimension completely.  I'd guess we still want the dimension, just not in the chart.  I agree with concatenating Table_B, however, which is what I was suggesting.

                   

                  QueueWeRavn, I can't think of any good reason to leave these tables separated in the model.  And if there IS a good reason (not evident from the fields we were given), I can't think of any good reason not to add the Category field to Table B during the load.  Calculated dimensions tend to be slower than real dimensions, plus you then can't do things like select category W, though in some cases it IS an advantage to have a solution that doesn't require a reload, so good job there.  Note also that we'll get a synthetic key either way.  This isn't strictly a problem, but IS a hint that we should perhaps combine the tables.