5 Replies Latest reply: Sep 7, 2014 11:02 PM by Jonathan Poole RSS

    Concatenate two CrossTables

      Hi all,

       

      May I know anyone has any suggestions about how to concatenate two CrossTables, please? Almost all the fields in the two CrossTables are the same except the attribute fields data are different (although the same fields names and different data name after CrossTable) and will be used for later calculations.

       

      Many thanks,

       

      Grace

        • Re: Concatenate two CrossTables
          Manish Kachhia

          If both tables have same field names and different data, there is no need of Concatenate... they will concatenate automatically ...

           

          CrossTable(Year,Sales)

          Load * Inline

          [

            Name, 2010, 2011

            A, 100, 70

            B, 110, 65

            C, 120, 55

            D, 130, 70

          ];

           

          CrossTable(Year,Sales)

          Load * Inline

          [

            Name, 2012, 2013

            A, 300, 400

            B, 100, 230

            C, 300, 210

            D, 200, 100

           

           

          ];

            • Re: Concatenate two CrossTables

              Hi Manish,

               

              Thank you for your reply. However, the field names are not exactly the same. The resulting field names after the two CrossTable are different so that I can use resulting field named A to divide by field named B. In this case, Qlikview won't do the "Auto Concatenation". In fact, when I just simply load in the two CrossTables, Qlikview just get stucks there.

               

              May I know how I should deal with it, please?

               

              Many thanks,

               

              Grace

            • Re: Concatenate two CrossTables
              Deepak Vadithala

              Hi Grace,

               

              As mentioned by Manish, QlikView will do the "Auto Concatenation" when you have two tables with same schema (I.e. In this case, same field names). No matter whether you load them immediately (I.e. one after another) or load them in different tabs with code in between; QlikView still performs "Auto Concatenation".

               

              I've tried a video tutorial on Crosstable and Concatenation, NoConcatenation and Auto Concatenation on my blog. You can use the following links to access the videos:

               

              http://qlikshare.com/qlikview-video-tutorial-crosstable-qlikview-transpose-data/

               

              http://qlikshare.com/qlikview-video-tutorial-concatenate-noconcatenate-operators-qlikview/

               

              I hope this helps!

               

              Cheers,

              DV

               

              www.QlikShare.com

                • Re: Concatenate two CrossTables

                  Hi Deepak,

                   

                  Thank you for your reply. However, the field names are not exactly the same. The resulting field names after the two CrossTable are different so that I can use resulting field named A to divide by field named B. In this case, Qlikview won't do the "Auto Concatenation". In fact, when I just simply load in the two CrossTables, Qlikview just get stucks there.

                   

                  May I know how I should deal with it, please?

                   

                  Many thanks,

                   

                  Grace

                    • Re: Concatenate two CrossTables
                      Jonathan Poole

                      It sounds like you want to JOIN the tables together. When you join each row in the 2nd table will be matched to any row in the first table that has the same values in all the fields that have the same name between the files.

                       

                      Once you have joined the files you can do a resident load and calculate field A by field B to get a new calculated field.  Something like the following. Left join will preserve all rows in table A even if no match is found in B.

                       

                      DataTemp:

                      ...Code for Crosstable load A...

                       

                      left join (DataTemp)

                      ....Code for Crosstable load B...

                       

                      noconcatenate

                      Data:

                      load

                           *,

                           A/B as  Newfield

                      resident DataTemp;

                       

                      drop table DataTemp;