Re: Reference a piece of script rather than copy/p... - Qlik Community

stevelord · ‎2014-02-27

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');

Nicole-Smith · ‎2014-02-27

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?

Gysbert_Wassenaar · ‎2014-02-27

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);

talk is cheap, supply exceeds demand

stevelord · ‎2014-02-27

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.

Nicole-Smith · ‎2014-02-27

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)

stevelord · ‎2014-02-27

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?)

Nicole-Smith · ‎2014-02-28

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)