Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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');
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?
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);
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.
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
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?)
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