18 Replies Latest reply: Jan 4, 2015 11:41 AM by Mario Estrada RSS

    Concatenate or join ?

      Hi all,

       

      I am breaking  my head on following issue. I tried join and concatenate but in table viewer i can't see correct results.I

      got three billing months data as.

      Dec :                                                              

                                                                           

      Load

      Connection_No,

      Date,

      Name,

      Bill,

      Arrears,

      Power_consumed,

      Loss

      From DEC_2009

       

      Jan:

      Load

      Connection_No,

      Date,

      Name,

      Bill,

      Arrears,

      Power_consumed,

      Loss

      From JAN_2010

       

      Feb:

      Load

      Connection_No,

      Date,

      Name,

      Bill,

      Arrears,

      Power_consumed,

      Loss

       

      From FEB_2010

       

      Later on I need to calculate Connection number,Should I concatenate or Join.

       

      experts can you help me on this issue please.

       

      Thanks,

      JK

        • Re: Concatenate or join ?
          Nagaian Krishnamoorthy

          Concatenate

          • Re: Concatenate or join ?
            neetha P

            Hi,

             

            Use Concatenate:

             

            Dec:

             

            Load

            Connection_No,

            Date,

            Name,

            Bill,

            Arrears,

            Power_consumed,

            Loss

            From DEC_2009;


            Concatenate


            Jan:

            Load

            Connection_No,

            Date,

            Name,

            Bill,

            Arrears,

            Power_consumed,

            Loss

            From JAN_2010;


            Concatenate


            Feb:

            Load

            Connection_No,

            Date,

            Name,

            Bill,

            Arrears,

            Power_consumed,

            Loss


            From FEB_2010

            • Re: Concatenate or join ?
              Colin Albert

              The load will auto-concatenate the data to a single table containing the data from all source files because the field names are identical in each table being loaded. There is no need to add a concatenate command.

              The table viewer should show one table containing the total number of rows fromDEC_2009, JAN_2010 and FEB_2010.

              • Re: Concatenate or join ?

                Hi Janaki,

                 

                No need to do Concatenate in your case as all the columns are same so, autoconcatenate will come into picture.

                 

                If you want to calculate sum(Connection_No) then it will give sum of total tables. But in case if you want to calculate each table, for example sum(Connection_No) for only Dec table, then you can't, to do that you need to add Flag for each table as I placed below. so you can use sum(Connection_No) per each table by using Set analysis.

                Ex: To get DEC sum of Connection_No= sum({<Type={'Dec'}>}Connection_No).

                To get Jan sum of Connection_No= sum({<Type={'Jan'}>}Connection_No).

                To get Feb sum of Connection_No= sum({<Type={'Feb'}>}Connection_No).

                 

                Code: Just add one more column as below.

                 

                Dec:                                                      

                Load

                Connection_No,

                Date,

                Name,

                Bill,

                Arrears,

                Power_consumed,

                Loss,

                'Dec' as Type

                From DEC_2009;

                 

                Jan:

                Load

                Connection_No,

                Date,

                Name,

                Bill,

                Arrears,

                Power_consumed,

                Loss,

                'Jan' as Type

                From JAN_2010;

                 

                Feb:

                Load

                Connection_No,

                Date,

                Name,

                Bill,

                Arrears,

                Power_consumed,

                Loss,

                'Feb' as Type

                From FEB_2010

                 

                 

                Thank you,

                • Re: Concatenate or join ?
                  Mario Estrada

                  Load

                  Connection_No,

                  Date,

                  month(Date) AS month,

                  year(Date) As year,

                  Name,

                  Bill,

                  Arrears,

                  Power_consumed,

                  Loss

                  From WHATEVERTHEFILE;

                   

                  no matter how many loads you perform, can even go automatically using a loop., Qview will concatenate them automatically for you. and then in expression just refer to the month and year as follows:

                   

                  in a chart. dimension, year,month and put sum(Power_consumed) in an expression.

                  from the text boxes

                  ='Dec ' & chr(13) & SUM({1 <month={12},year={2014}>} Power_consumed)

                                              or


                  ='Dec ' & chr(13) & COUNT({1 <month={12},year={2014}>} DISTINCT Connection_No )


                  above for showing number of connections numbers in 2014 dec.