19 Replies Latest reply: May 3, 2017 2:31 AM by Priya A RSS

    To sum in script

    Priya A

      I have 3 columns like basicpay, hra, Variable pay I have to add these 3 columns .But while using sum function it accepts only 1 parameter.Please help  me find out the code  which I want  to be done in  script and to display the values in the dashboard...

       

       

         

      BasicHRAVariable payTotal
      4000300015008500
        • Re: To sum in script
          Saniya Shaikh

          Hi,

           

          You can do this in the Dashboard or frontend.

          Try sum(Basic) + Sum(HRA) + Sum(Variable Pay)

           

          regards,

          Saniya.

          • Re: To sum in script
            surendra j

            try with rangesum

            rangesum(Basic,HRA,Variable pay)

            • Re: To sum in script
              Yoganantha Prakash G P

              Priya,

              Simply try with the following expression

               

              Aggr(Sum(Basic+HRA+[Variable Pay]),EmpID)


              I got the output as below

              Screenshot_40.png

              as you needed.........

              I think it is useful

              • Re: To sum in script
                paridhi mantri

                Hi ,

                 

                Direcly use this fields and create new field like-

                 

                Basic + HRA+ Variable Pay as Total


                If you need do group by,You will get expected result.


                Thanks

                Paridhi


                • Re: To sum in script
                  Yoganantha Prakash G P

                  If you got answer kindly mark it as correct answer so that it will be useful for others

                    • Re: To sum in script
                      Priya A

                      If( Primary_skills='Informatica',33, 

                         If( Primary_skills='.Net',33,

                         If( Primary_skills='Python',33 ,

                         If( Primary_skills='Java',33)))) As [p_weightage],

                          

                         if (subskills_1= 'DW',33,

                         if ( subskills_1= 'Qlikview',33,

                         if (subskills_1= 'Data Network',33,

                         if ( subskills_1= ' Mainframe Testing',33,

                         if (subskills_1= 'Java Enterprise - Front End',33,

                         if (subskills_1= 'Java Enterprise - Front End',33)))))) as [s1_weightage],

                        

                        

                         if(subskills_2= 'SSIS',33,

                         if( subskills_2= 'Qliksense',33, 

                         if( subskills_2= '.Net Framework',33,

                         if( subskills_2= 'Mobile Application Testing',33,

                          if( subskills_2= 'Java Enterprise - Back End',33,

                         if( subskills_2= 'EJP',33)))))) as [s2_weightage],

                           subskills_1,

                           subskills_2

                      FROM

                      [..\Users\PA00484024\Downloads\Sample Employee Data..crct.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                      LOAD EMP_NO,

                        [p_weightage],

                         [s1_weightage],

                         [s2_weightage],

                        ([p_weightage]+[s1_weightage]+[s2_weightage]) as Total

                       

                        FROM

                      [..\Users\PA00484024\Downloads\Sample Employee Data..crct.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                       

                       

                       

                      This is my actual code. It is showing error that [p_weightage] not found.....

                        • Re: To sum in script
                          Shiva Nagesh

                          Priya, After calculating the values in a table, use resident load like below.

                           

                          Test_Table:

                           

                          LOAD EMP_NO,

                            [p_weightage],

                             [s1_weightage],

                             [s2_weightage],

                            ([p_weightage]+[s1_weightage]+[s2_weightage]) as Total;

                           

                          If( Primary_skills='Informatica',33,

                             If( Primary_skills='.Net',33,

                             If( Primary_skills='Python',33 ,

                             If( Primary_skills='Java',33)))) As [p_weightage],

                             

                             if (subskills_1= 'DW',33,

                             if ( subskills_1= 'Qlikview',33,

                             if (subskills_1= 'Data Network',33,

                             if ( subskills_1= ' Mainframe Testing',33,

                             if (subskills_1= 'Java Enterprise - Front End',33,

                             if (subskills_1= 'Java Enterprise - Front End',33)))))) as [s1_weightage],

                           

                           

                             if(subskills_2= 'SSIS',33,

                             if( subskills_2= 'Qliksense',33,

                             if( subskills_2= '.Net Framework',33,

                             if( subskills_2= 'Mobile Application Testing',33,

                              if( subskills_2= 'Java Enterprise - Back End',33,

                             if( subskills_2= 'EJP',33)))))) as [s2_weightage],

                               subskills_1,

                               subskills_2

                          FROM

                          [..\Users\PA00484024\Downloads\Sample Employee Data..crct.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                            • Re: To sum in script
                              Shiva Nagesh

                              Close the thread, if you got your answer or add comment if you still facing any issue.

                                • Re: To sum in script
                                  Priya A

                                  [empdata]:

                                  LOAD EMP_NO,

                                  -----

                                  -----

                                  --------

                                     

                                       Primary_skills,

                                   

                                    If( Primary_skills='Informatica',33, 

                                     If( Primary_skills='.Net',33,

                                     If( Primary_skills='DW',33 ,

                                     If( Primary_skills='Java',33)))) As [p_weightage],

                                      

                                     if (subskills_1= 'DW',33,

                                     if ( subskills_1= 'Qlikview',33,

                                     if (subskills_1= 'Data Network',33,

                                     if ( subskills_1= ' Mainframe Testing',33,

                                     if (subskills_1= 'Java Enterprise - Front End',33,

                                     if (subskills_1= 'Java Enterprise - Front End',33)))))) as [s1_weightage],

                                    

                                    

                                     if(subskills_2= 'SSIS',33,

                                     if( subskills_2= 'Qliksense',33, 

                                     if( subskills_2= '.Net Framework',33,

                                     if( subskills_2= 'Mobile Application Testing',33,

                                      if( subskills_2= 'Java Enterprise - Back End',33,

                                     if( subskills_2= 'EJP',33)))))) as [s2_weightage],

                                   

                                   

                                       subskills_1,

                                       subskills_2

                                      

                                    

                                   

                                   

                                  FROM

                                  [..\Users\PA00484024\Downloads\Sample Employee Data..crct.xlsx]

                                  (ooxml, embedded labels, table is Sheet1);

                                   

                                   

                                  [Weightage]:    

                                  LOAD [EMP_NO],

                                    [p_weightage],

                                     [s1_weightage],

                                     [s2_weightage],

                                    ([p_weightage]+[s1_weightage]+[s2_weightage]) as Total Resident[empdata] Group by [EMP_NO];

                                  test1.png

                                  Still it is showing error..after using resident load..

                                    • Re: To sum in script
                                      Shiva Nagesh

                                      Where is the aggregation in this part of code??

                                       

                                      LOAD [EMP_NO],

                                        [p_weightage],

                                         [s1_weightage],

                                         [s2_weightage],

                                        ([p_weightage]+[s1_weightage]+[s2_weightage]) as Total Resident[empdata] Group by [EMP_NO];

                                       

                                      May be it should be like this

                                       

                                      LOAD [EMP_NO],

                                        sum([p_weightage]),

                                         sum([s1_weightage]),

                                         sum([s2_weightage]),

                                        (sum([p_weightage])+sum([s1_weightage])+sum([s2_weightage])) as Total Resident[empdata] Group by [EMP_NO];

                                       

                                      Please share your application if possible.

                                        • Re: To sum in script
                                          Priya A

                                          thank you.. Its working

                                          • Re: To sum in script
                                            Priya A

                                            Weightage:

                                            LOAD EMP_NO,

                                            sum ([p_weightage]),

                                              sum ([s1_weightage]),

                                               sum([s2_weightage]),

                                              (sum([p_weightage])+sum([s1_weightage])+sum([s2_weightage])) as Total Resident empdata group by EMP_NO ;

                                             

                                            i want to give if statement .. like if i get total as 66  i.e considering as 75% , i want to give 'fastrack' training

                                            and if total as 33 i.e considering as 50%, i want give  'standard' training..

                                             

                                            if ([Total]= 75, 'Fastrack',

                                            if ([Total]=50,'Standard')) as training ;

                                             

                                            Help me with the correct code.

                                            • Re: To sum in script
                                              Priya A

                                              ,

                                              image4.png,

                                               

                                              hello,

                                               

                                              I am actually developing a application called resume search with the the help of qlikview visualiation. this question is one step ahead of which you answered rightly above.

                                              I have

                                               

                                              PRIMARY_SKILLS,

                                                        SUBSKILLS_1,

                                                        SUBSKILLS_2.

                                               

                                               

                                              I want to give total 100 if only primary skills are searched and selected.

                                               

                                              And if primary_skills and one subskills_1  is searched and selected.

                                              The total value should be distributed as 60 to primary_skills and 40 to subskills_1.

                                               

                                              And if the three are searched and selected the  total value should be distributed as

                                              60 to primary_skills,

                                              20 each to subskills_1 and subskills_2.

                                               

                                              How to do this. Please help