6 Replies Latest reply: Feb 17, 2016 9:38 AM by s j RSS

    Help

    Gourav Sikka

      Hi,

        I have one requirement..Suppose I have 2 excel file Same Column Name......When I pull in qlikview It should be One File....Means 3 File See like ..When I show in table Box....Then sum Of All Values Apr May Jun in one Table....Please Help Me...

      Header 1Header 2Header 3Header 4
      ParticulersApr 2015May 2015Jun 2015
      Net revenue121314
      Net Cost111013
      Net material101413

       

      Header 1Header 2Header 3Header 4
      ParticulersApr 2015May 2015Jun 2015
      Net revenue131619
      Net Cost111713
      Net material121115
      Header 1Header 2Header 3Header 4
      ParticulersApr 2015May 2015Jun 2015
      Net revenue252933
      Net Cost222726
      Net material222528

      @ !

        • Re: Help
          Sunny Talwar

          Try this:

           

          Table1:

          LOAD * INLINE [

              Particulars, Apr 2015, May 2015, Jun 2015

              Net revenue, 12, 13, 14

              Net Cost, 11, 10, 13

              Net material, 10, 14, 13

          ];

           

          Concatenate (Table1)

          LOAD * INLINE [

              Particulars, Apr 2015, May 2015, Jun 2015

              Net revenue, 13, 16, 19

              Net Cost, 11, 17, 13

              Net material, 12, 11, 15

          ];

           

          Concatenate (Table1)

          LOAD * INLINE [

              Particulars, Apr 2015, May 2015, Jun 2015

              Net revenue, 25, 29, 33

              Net Cost, 22, 27, 26

              Net material, 22, 25, 28

          ];

           

          FinalTable:

          NoConcatenate

          LOAD Particulars,

            Sum([Apr 2015]) as [Apr 2015],

            Sum([May 2015]) as [May 2015],

            Sum([Jun 2015]) as [Jun 2015]

          Resident Table1

          Group By Particulars;

           

          DROP Table Table1;


          Capture.PNG

            • Re: Help
              Gourav Sikka

              Hi

                I want to Show my list Box Regarding Apr 2015, May 2015, Jun 2015 

                                                     25       29       33

                                                                               22          27            26

                                                                               22          25           28

               

              When I select Net Revenue & Apr 2015 Then It show Be 25 Value 

              When I Select Net Cost & Apr 2015 Then It Show be      22  Value

              When I Select Net Material & Apr 2015  then It Show be      22  Value

                • Re: Help
                  Sunny Talwar

                  Oh the third table you put was the expected output?

                  • Re: Help
                    Sunny Talwar

                    Try this:

                     

                    Table1:

                    LOAD * INLINE [

                        Particulars, Apr 2015, May 2015, Jun 2015

                        Net revenue, 12, 13, 14

                        Net Cost, 11, 10, 13

                        Net material, 10, 14, 13

                    ];

                     

                    Concatenate (Table1)

                    LOAD * INLINE [

                        Particulars, Apr 2015, May 2015, Jun 2015

                        Net revenue, 13, 16, 19

                        Net Cost, 11, 17, 13

                        Net material, 12, 11, 15

                    ];

                     

                    FinalTable:

                    NoConcatenate

                    LOAD Particulars,

                      Sum([Apr 2015]) as [Apr 2015],

                      Sum([May 2015]) as [May 2015],

                      Sum([Jun 2015]) as [Jun 2015]

                    Resident Table1

                    Group By Particulars;

                     

                    DROP Table Table1;

                  • Re: Help
                    s j

                    I will suggest you to implement KPI calculation at backend corresponding to period instead of hardcoding month and year .

                     

                    If you are doing KPI calculation at back end , it is easy to achieve your report format. below format like

                    Particulers, Apr 2015, May 2015, Jun 2015  in inline table will not help you because for every additional month, it require script change.

                  • Re: Help
                    Manish Kachhia
                    Data:
                    CrossTable(MonthYear, Value)
                    Load * Inline
                    [
                      Particulers, Apr 2015, May 2015, Jun 2015
                      Net revenue, 12, 13, 14
                      Net Cost, 11, 10, 13
                      Net material, 10, 14, 13
                    ];
                    
                    
                    CrossTable(MonthYear, Value)
                    Load * Inline
                    [
                      Particulers, Apr 2015, May 2015, Jun 2015
                      Net revenue, 13, 16, 19
                      Net Cost, 11, 17, 13
                      Net material, 12, 11, 15
                    ];
                    
                    
                    CrossTable(MonthYear, Value)
                    Load * Inline
                    [
                      Particulers, Apr 2015, May 2015, Jun 2015
                      Net revenue, 25, 29, 33
                      Net Cost, 22, 27, 26
                      Net material, 22, 25, 28
                    ];
                    
                    
                    NoConcatenate
                    Final:
                    Load Particulers, MonthYear, SUM(Value) as Value Resident Data
                    Group By Particulers, MonthYear;
                    
                    
                    Drop Table Data;