4 Replies Latest reply: Feb 22, 2017 12:54 PM by Jenny Smith RSS

    Uploading Variables

    Jenny Smith

      Hi all,

       

      I am trying to upload a list of variables into my script from an excel file - previously I had a tab on the UI that I manually updated each month but wanted to automate it as much as possible.

       

      I have created a Load statement as below but the variables do not seem to be changing when I reload. I use the variables later in the script to calculate costs e.g Hourly rate x Time on site etc.

       

      Would be grateful for some help!

       

      LOAD Variable,

           Value

           FROM

      [Mapping data\Variables.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      Let $([Subcontractors]) =[Subcontractors];

      Let $([Materials]) = [Materials];

      Let $([Hourly Rate]) = [Hourly Rate];

      Let $([Delivery Cost]) = [Delivery Cost];

        • Re: Uploading Variables
          Andrey Khoronenko

          Hi Jenny,

           

          Maybe so?

           

          LOAD Variable,

               Value

               FROM

          [Mapping data\Variables.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          Let $([Subcontractors]) = $([Subcontractors]);

          Let $([Materials]) = $([Materials]);

          Let $([Hourly Rate]) = $([Hourly Rate]);

          Let $([Delivery Cost]) = $([Delivery Cost]);

           

          Regards,

          Andrey

            • Re: Uploading Variables
              Jenny Smith

              Hi Andrey,

               

              I got the variables to work last week by using the below script:

               

              Let vVariable1 = fieldvalue('Variable',1);

              let [Subcontractors Birmingham] = fieldvalue('VarValue',1);

              let $(vVariable1) = $(Subcontractors Birmingham);

               

               

              Let vVariable2 = fieldvalue('Variable',2);

              let [Materials Birmingham] = fieldvalue('VarValue',2);

              let $(vVariable2) = $(Materials Birmingham);

               

               

              Let vVariable3 = fieldvalue('Variable',3);

              let [Hourly Rate Birmingham] = fieldvalue('VarValue',3);

              let $(vVariable3) = $(Hourly Rate Birmingham);

               

               

              Let vVariable4 = fieldvalue('Variable',4);

              let [Delivery Cost Birmingham] = fieldvalue('VarValue',4);

              let $(vVariable4) = $(Delivery Cost Birmingham);

               

              etc, etc.

               

              It worked last week but now comes up with the below error - it looks like it is using the first few variables from my table but then not the rest?

               

               

              Error in expression:

              * is not a valid function

               

               

              ServData:

              LOAD [Contract Number] as [Contract Num],

                   [Premises Number],

                   [Service Branch Number],

                   [Service Branch Name],

                   [Negotiating Branch Number],

                   [Negotiating Branch Name],

                   [Total time],

                   [Total Calls],

                   [Contract Number]&'-'& [Premises Number] as UID,

                   applymap('KAlocal',[Negotiating Branch Number],'Other') as [KA/Local2],

                   applymap('ProductDesc2',[Product Code],'Other') as [ProductDesc],

                   applymap('ProductGroup2',[Product Code],'Other') as [ProductGroup],

                   applymap('CallCategory', [Negotiating Branch Number],'Other') as CallCategory,

                  

                  

                   if([Service Branch Number]='21' and ([Negotiating Branch Number]<>'98'),20.79*([Total time]/60),

                   if([Service Branch Number]='22' and ([Negotiating Branch Number]<>'98'),19.25*([Total time]/60),

                   if([Service Branch Number]='23' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

                   if([Service Branch Number]='24' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

                   if([Service Branch Number]='26' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

                   if([Service Branch Number]='27' and ([Negotiating Branch Number]<>'98'),*([Total time]/60),

                   if([Service Branch Number]='21' and ([Negotiating Branch Number]='98'),*([Total time]/60),

                   if([Service Branch Number]='22' and ([Negotiating Branch Number]='98'),*([Total time]/60),

                   if([Service Branch Number]='23' and ([Negotiating Branch Number]='98'),*([Total time]/60),

                   if([Service Branch Number]='24' and ([Negotiating Branch Number]='98'),*([Total time]/60),

                   if([Service Branch Number]='26' and ([Negotiating Branch Number]='98'),*([Total time]/60),

                   if([Service Branch Number]='27' and ([Negotiating Branch Number]='98'),*([Total time]/60),0)))))))))))) as [Direct Service Cost],

                  

                   if([Service Branch Number]='21' and ([Negotiating Branch Number]<>'98'),120.72*[Total Calls],

                   if([Service Branch Number]='22' and ([Negotiating Branch Number]<>'98'),130.32*[Total Calls],

                   if([Service Branch Number]='23' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

                   if([Service Branch Number]='24' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

                   if([Service Branch Number]='26' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

                   if([Service Branch Number]='27' and ([Negotiating Branch Number]<>'98'),*[Total Calls],

                   if([Service Branch Number]='21' and ([Negotiating Branch Number]='98'),*[Total Calls],

                   if([Service Branch Number]='22' and ([Negotiating Branch Number]='98'),*[Total Calls],

                   if([Service Branch Number]='23' and ([Negotiating Branch Number]='98'),*[Total Calls],

                   if([Service Branch Number]='24' and ([Negotiating Branch Number]='98'),*[Total Calls],

                   if([Service Branch Number]='26' and ([Negotiating Branch Number]='98'),*[Total Calls],

                   if([Service Branch Number]='27' and ([Negotiating Branch Number]='98'),*[Total Calls],0)))))))))))) as [Delivery Cost]

               

              FROM

              [Visits\Visits*.xlsx]

              (ooxml, embedded labels, table is Data)   

               

               

              WHERE([Service Date] > '31/01/2016' and [Service Date] < '01/02/2017')