6 Replies Latest reply: Feb 28, 2014 8:40 AM by Nicole Smith RSS

    Reference a piece of script rather than copy/paste it over and over

    Steve Lord

      Hi, I have a complex set of script designed to pivot data in a database table and need to have the script run for one client at a time (sequentially) rather than all clients at once.  I would constrain the script with a "Where Client=<abcclient>" and just change that where statement for each run.

       

      Can someone provide me the syntax needed to assign a name to that script then to run that script by name instead of by all the re-pasting all the stuff inside the script?

       

      Thanks!

       

      Here's the script chock full of aggregation statements, group by, and where constraints:

      (The script works okay most times and sometimes not, and I attribute the failures to competing traffic at different times or days since the only difference is the day or time of day.  Adding 'where client='clientname' is the change that allows it to breeze through, I just don't want a million tabs of the same script for each client.)

       

      BiometricsPivoted:

      LOAD UserId,

           Source as [Biometrics Source],

           Only(if(TestName='Systolic' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Systolic,

           Only(if(TestName='Diastolic' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Diastolic,

           Only(if(TestName='Pulse Rate' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as [Pulse Rate],

           Only(if(TestName='BMI' AND TestValue>10 AND TestValue<150,TestValue, Null())) as ZBMI,

           Only(if(TestName='Fasting' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Fasting,

           Only(if((TestName='NonFasting' OR TestName='Non-Fasting') AND TestValue>1 AND TestValue<1000,TestValue,Null())) as NonFasting,

           Only(if(TestName='HbA1c' AND TestValue>0 AND TestValue<100,TestValue/100,Null())) as HbA1c, //divided by 100 to show as percentage 'unit' in tables

           Only(if(TestName='Total Cholesterol' AND TestValue>1 AND TestValue<5000,TestValue,Null())) as [ZTotal Cholesterol],

           Only(if(TestName='HDL' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as ZHDL,

           Only(if(TestName='LDL' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as ZLDL,

           Only(if(TestName='Triglycerides' AND TestValue>1 AND TestValue<5000,TestValue,Null())) as ZTriglycerides,

           Only(if(TestName='Cholesterol Ratio' AND TestValue>=1 AND TestValue<1000,TestValue,Null())) as [ZCholesterol Ratio],

           Only(if((TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)' OR TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)')

           AND TestValue>1 AND TestValue<1000,TestValue,Null())) as [Waist Circumference],

           Only(if((TestName='Body Fat (Male)' OR TestName='Body Fat (Female)') AND TestValue>0 AND TestValue<100,TestValue/100,Null()))

           as [Body Fat], //divided by 100 to show as percentage 'unit' in tables

           Only(if((TestName='Waist-To-Hip Ratio (Male)' OR TestName='Waist-To-Hip Ratio (Female)') AND TestValue>0 AND TestValue<100,

           TestValue,Null())) as [Waist To Hip Ratio],

           Only(if(TestName='Weight' AND TestValue>1 AND TestValue<750,TestValue,Null())) as Weight,

           Only(if(TestName='HeightFt' AND TestValue>1 AND TestValue<12,TestValue,Null())) as HeightFt,

           Only(if(TestName='HeightIn' AND TestValue>=0 AND TestValue<12,TestValue,Null())) as HeightIn,

           Only(if((TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)') AND TestValue>0 AND TestValue<120,TestValue,Null())) as [Waist Circumference (Male)],

           Only(if((TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)') AND TestValue>0 AND TestValue<120,TestValue,Null())) as [Waist Circumference (Female)],

           Only(if(TestName='Body Fat (Male)' AND TestValue>0 AND TestValue<100,TestValue/100,Null()))

           as [Body Fat (Male)], //divided by 100 to show as percentage 'unit' in tables

           Only(if(TestName='Body Fat (Female)' AND TestValue>0 AND TestValue<100,TestValue/100,Null()))

           as [Body Fat (Female)], //divided by 100 to show as percentage 'unit' in tables

           Only(if(TestName='Waist-To-Hip Ratio (Male)' AND TestValue>0 AND TestValue<10, TestValue,Null())) as [Waist To Hip Ratio (Male)],

           Only(if(TestName='Waist-To-Hip Ratio (Female)' AND TestValue>0 AND TestValue<10, TestValue,Null())) as [Waist To Hip Ratio (Female)],

           Only(if(TestName='Tobacco User' AND TestValue=0, Dual('No',1), if(TestName='Tobacco User' AND TestValue=1, Dual('Yes',2)))) as [Tobacco User],

           TimeStamp(TestDate) as TestDate,

           Year(Date(TestDate)) as TestYear,

           Month(Date(TestDate)) as TestMonth,

           Year(Date(Floor(TestDate)))&' '&Num(Month(Date(Floor(TestDate))),'00') as TestMonthYear,

           Num(Month(Date(Floor(TestDate))),'00')&'/'&NUM(Day(Date(Floor(TestDate))),'00')&'/'&Num(Year(Date(Floor(TestDate))),'0000') as TestMonthDayYear

      FROM

      filepath\Biometrics2.qvd

      (qvd) WHERE ClientAccountName='ABCClient' AND Year(TestDate)>2007 AND TestDate<=Now() AND TestDate<=ImportedDate

      Group By UserId, Source, TimeStamp(TestDate), Year(Date(TestDate)), Month(Date(TestDate)), Year(Date(Floor(TestDate)))&' '&Num(Month(Date(Floor(TestDate))),'00'),

           Num(Month(Date(Floor(TestDate))),'00')&'/'&NUM(Day(Date(Floor(TestDate))),'00')&'/'&Num(Year(Date(Floor(TestDate))),'0000');

        • Re: Reference a piece of script rather than copy/paste it over and over
          Nicole Smith

          You'll want a for each loops that looks something like the one in this post: How to use List more efficiently in For Each...Next?

          • Re: Reference a piece of script rather than copy/paste it over and over
            Gysbert Wassenaar

            Use a variable for the client. Change the value of the variable as needed.

             

            SET vClient = ClientNameHere;

            LOAD .... FROM ... WHERE ClientAccountName = '$(vClient)';

             

            Or perhaps you're looking for a subroutine

             

            SUB LoadOneClient(ClientNameHere)

            LOAD .... FROM ... WHERE ClientAccountName = '$(ClientNameHere)';

            END SUB

             

            CALL LoadOneClient(ClientA);

            CALL LoadOneClient(ClientB);

            ....

            CALL LoadOneClient(ClientX);

              • Re: Reference a piece of script rather than copy/paste it over and over
                Steve Lord

                Hi Gysbert, can you provide a little more detail by putting that around the script I provided as it would appear for me?  Keeping in mind the ClientAccountName is a field within the table and I want the load just the first client’s data, pivot it and finish, then load and pivot the next client’s data, and so on.  I was about to copy/paste the load script a bunch of times with a ‘where clientaccountname=abc’, then load again with ‘where clientaccountname=123’ and so on for each client, but am trying to make the script do that itself.

                  • Re: Reference a piece of script rather than copy/paste it over and over
                    Nicole Smith

                    For the for each loop, you could do something that looks like this:

                     

                    FOR Each a in 'ClientAccountName1','ClientAccountName2','ClientAccountName3'

                         .....Your script.....

                         WHERE clientaccountname = $(a)

                    NEXT

                      • Re: Reference a piece of script rather than copy/paste it over and over
                        Steve Lord

                        Nicole, I think yours is closest so far.  Gysbert or Nicole or anyone, can someone help me with the syntax to setup the variable for a, or am I okay with plopping the actual list of clients into that spot instead of a variable?  (Would I need brackets, commas, stuff around it?)

                          • Re: Reference a piece of script rather than copy/paste it over and over
                            Nicole Smith

                            You can either just list them like I have in my last post, or create a variable like this:

                            SET Variable = 'ClientAccountName1','ClientAccountName2','ClientAccountName3';

                             

                            If you go the variable route, the first line of your for loop will looks like this:

                            FOR Each a in $(Variable)

                             

                            So your script should look something like:

                            FOR Each a in $(Variable)

                            BiometricsPivoted:

                            LOAD UserId,

                                 Source as [Biometrics Source],

                                 Only(if(TestName='Systolic' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Systolic,

                                 Only(if(TestName='Diastolic' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Diastolic,

                                 Only(if(TestName='Pulse Rate' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as [Pulse Rate],

                                 Only(if(TestName='BMI' AND TestValue>10 AND TestValue<150,TestValue, Null())) as ZBMI,

                                 Only(if(TestName='Fasting' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as Fasting,

                                 Only(if((TestName='NonFasting' OR TestName='Non-Fasting') AND TestValue>1 AND TestValue<1000,TestValue,Null())) as NonFasting,

                                 Only(if(TestName='HbA1c' AND TestValue>0 AND TestValue<100,TestValue/100,Null())) as HbA1c, //divided by 100 to show as percentage 'unit' in tables

                                 Only(if(TestName='Total Cholesterol' AND TestValue>1 AND TestValue<5000,TestValue,Null())) as [ZTotal Cholesterol],

                                 Only(if(TestName='HDL' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as ZHDL,

                                 Only(if(TestName='LDL' AND TestValue>1 AND TestValue<1000,TestValue,Null())) as ZLDL,

                                 Only(if(TestName='Triglycerides' AND TestValue>1 AND TestValue<5000,TestValue,Null())) as ZTriglycerides,

                                 Only(if(TestName='Cholesterol Ratio' AND TestValue>=1 AND TestValue<1000,TestValue,Null())) as [ZCholesterol Ratio],

                                 Only(if((TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)' OR TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)')

                                 AND TestValue>1 AND TestValue<1000,TestValue,Null())) as [Waist Circumference],

                                 Only(if((TestName='Body Fat (Male)' OR TestName='Body Fat (Female)') AND TestValue>0 AND TestValue<100,TestValue/100,Null()))

                                 as [Body Fat], //divided by 100 to show as percentage 'unit' in tables

                                 Only(if((TestName='Waist-To-Hip Ratio (Male)' OR TestName='Waist-To-Hip Ratio (Female)') AND TestValue>0 AND TestValue<100,

                                 TestValue,Null())) as [Waist To Hip Ratio],

                                 Only(if(TestName='Weight' AND TestValue>1 AND TestValue<750,TestValue,Null())) as Weight,

                                 Only(if(TestName='HeightFt' AND TestValue>1 AND TestValue<12,TestValue,Null())) as HeightFt,

                                 Only(if(TestName='HeightIn' AND TestValue>=0 AND TestValue<12,TestValue,Null())) as HeightIn,

                                 Only(if((TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)') AND TestValue>0 AND TestValue<120,TestValue,Null())) as [Waist Circumference (Male)],

                                 Only(if((TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)') AND TestValue>0 AND TestValue<120,TestValue,Null())) as [Waist Circumference (Female)],

                                 Only(if(TestName='Body Fat (Male)' AND TestValue>0 AND TestValue<100,TestValue/100,Null()))

                                 as [Body Fat (Male)], //divided by 100 to show as percentage 'unit' in tables

                                 Only(if(TestName='Body Fat (Female)' AND TestValue>0 AND TestValue<100,TestValue/100,Null()))

                                 as [Body Fat (Female)], //divided by 100 to show as percentage 'unit' in tables

                                 Only(if(TestName='Waist-To-Hip Ratio (Male)' AND TestValue>0 AND TestValue<10, TestValue,Null())) as [Waist To Hip Ratio (Male)],

                                 Only(if(TestName='Waist-To-Hip Ratio (Female)' AND TestValue>0 AND TestValue<10, TestValue,Null())) as [Waist To Hip Ratio (Female)],

                                 Only(if(TestName='Tobacco User' AND TestValue=0, Dual('No',1), if(TestName='Tobacco User' AND TestValue=1, Dual('Yes',2)))) as [Tobacco User],

                                 TimeStamp(TestDate) as TestDate,

                                 Year(Date(TestDate)) as TestYear,

                                 Month(Date(TestDate)) as TestMonth,

                                 Year(Date(Floor(TestDate)))&' '&Num(Month(Date(Floor(TestDate))),'00') as TestMonthYear,

                                 Num(Month(Date(Floor(TestDate))),'00')&'/'&NUM(Day(Date(Floor(TestDate))),'00')&'/'&Num(Year(Date(Floor(TestDate))),'0000') as TestMonthDayYear

                            FROM

                            filepath\Biometrics2.qvd

                            (qvd) WHERE ClientAccountName=$(a) AND Year(TestDate)>2007 AND TestDate<=Now() AND TestDate<=ImportedDate

                            Group By UserId, Source, TimeStamp(TestDate), Year(Date(TestDate)), Month(Date(TestDate)), Year(Date(Floor(TestDate)))&' '&Num(Month(Date(Floor(TestDate))),'00'),

                                 Num(Month(Date(Floor(TestDate))),'00')&'/'&NUM(Day(Date(Floor(TestDate))),'00')&'/'&Num(Year(Date(Floor(TestDate))),'0000');

                            NEXT