4 Replies Latest reply: Jun 17, 2011 9:22 AM by Anne Duffy RSS

    Sum 2 fields to create an option in another !!

      Hi Guys,

       

       

      I have only the personal edition so I can not post an example of my QV doc, I will try my bestest to describe my issue as clear as possible.

       

      I have a QV doc loaded from numerous Excel files!

       

      One of these files is called Accounts; there are 2 particular fields in this report that I am concerned with, [Number of Structured] and [Number of Unstructured].

       

      Now I have a second excel sheet called Customers, one of the fields is called [type of Query],

       

      What I wish is that ,in the script I sum the 2 fields from accounts and load it as a result  ( Auto Confos!!) in Customers, this is what I have tried and although it does change my results , it does not change it correctly.

       

      Please ignor the From part of the load I took it out because it was like 5 lines long

       

      Hope someone can help

       

      Thanks

       

      CONCATENATE (MasterTable)

      LOAD 

      Date(Date) as Date,

      Month(Date(Date)) as Month,

      Week(Date(Date)) as Week,

      Year(Date(Date)) as Year,   

      ([Number of Structured]+ [Number of Unstructured])='Auto Confos' as [Type of Query],

      'Customer Service' as Activity,

      'John' as [Assistant Manager]

      FROM

      example.MASTER LOG.xls]

      Where Date>'06/03/2011' and Date<today();

       

        • Sum 2 fields to create an option in another !!

          Blossomthe2nd wrote:

          What I wish is that ,in the script I sum the 2 fields from accounts and load it as a result  ( Auto Confos!!) in Customers,

           

          What is this means,? You are storingas Auto Confos in customer table?

           

          Sorry, didnot get your problem. Could you please post a sample application and explain your requirement (people who has license key would be able to open any application, so pl dont mind to post your sample application)

           

          -Sridhar

            • Sum 2 fields to create an option in another !!

              Hi Sridhar

               

              In the our Job , everytime a structured or unstructured event happens we also have to complete a "Auto Confo", as our excel files are manually updated I wish that every time we log a Structured or unstructured QV knows that we wuld have to do a Auto Confo, so our guys would not have to log an Auto Confo on customer.

               

              The account spreadsheet has a 2 columns Called Structured and another called Unstructured.

              These have numeric values , this is loaded into QV as activity Rollover.

               

              I have a second spreadsheet and its activity is customer. One of the columns on this is Type of Query. So I wish that the sum of  Struct and Unstruct is added to the number column in Customer and the column Type of query recognises it as Auto Confo.

               

              Help.bmp

               

              The above are the 2 spread sheets fso for example on the 7 March I would like QV to count 87 Types of query as Auto Confo,

               

              ( 87 being the sum of 38 and 49 Struct and Unstruct)

               

              Can this be done ?

            • Sum 2 fields to create an option in another !!
              Chris Cammers

              The field you have after Year would end up being a True False value and it's name would be "Type Of Query" Based on what your wrote I dont this that is what you want.

               

              this would work...

              ([Number of Structured]+ [Number of Unstructured]) as [Auto Confos],

              [Type of Query]

               

              but I am surprised that what you have even executes

                • Sum 2 fields to create an option in another !!

                  Hi Chris

                   

                  I think the satement as you provided will change the sum to a field called Auro Confos,

                   

                  However on reading your post a little light bulb went off,

                   

                  If I call the field Number and then the result of type of Query as Auto Confo it should work

                   

                  Thanks

                   

                  CONCATENATE (MasterTable)

                  LOAD 

                  Date(Date) as Date,

                  Month(Date(Date)) as Month,

                  Week(Date(Date)) as Week,

                  Year(Date(Date)) as Year,   

                  ([No. of Unstructured Rollovers]+[No. of Structured Rollovers])as Number,

                  'Auto Confirmations' as [Type of Query],

                  'Customer Service' as Activity,

                  'John' as [Assistant Manager]

                  FROM