Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stevelord
Valued Contributor

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

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

6 Replies

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

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

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
Valued Contributor

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

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

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

stevelord
Valued Contributor

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

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

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

Community Browser