topic How to use For loop in App Development

How to use For loop

Anonymous — Fri, 08 Dec 2017 10:22:29 GMT

Hello Guys,

I have around 200 different projects with unique ids which has :-

a. Start date

b. End date

My tables looks like this as shown below :-

ID Project	Start date(MMDDYYYY)	End Date(MMDDYYYY)
12	02/02/2015	20/06/2019
34	30/07/2016	04/05/2018
56	26/04/2016	06/07/2019

Using FOR loop I want to calculate

a. Difference between start date and end date in Days and in Months

Thanks In Advance

ravi

Re: How to use For loop

maxgro — Fri, 08 Dec 2017 10:37:13 GMT

I think you can calculate 2 new fields in the same table without a for loop

load * inline [

ID, StartDate, EndDate

12, 02/02/2015, 20/06/2019

34, 30/07/2016, 04/05/2018

56, 26/04/2016, 06/07/2019

100, 8/12/2017, 9/12/2017

100, 8/12/2017, 8/12/2017

100, 8/12/2017, 8/1/2018

];

LOAD

EndDate - StartDate as DaysDiff,

Num(((year(EndDate) * 12) + month(EndDate)) - (((year(StartDate) * 12) + month(StartDate))) + 1) as MonthsDiff

Resident T;

DROP Table T;

If you want a for loop

load * inline [

ID, StartDate, EndDate

12, 02/02/2015, 20/06/2019

34, 30/07/2016, 04/05/2018

56, 26/04/2016, 06/07/2019

100, 8/12/2017, 9/12/2017

100, 8/12/2017, 8/12/2017

100, 8/12/2017, 8/1/2018

];

for i=0 to NoOfRows('T') -1

LET vStart = Peek('StartDate', $(i), T);

LET vEnd = Peek('EndDate', $(i), T);

LET vDaysDiff = '$(vEnd)' - '$(vStart)';

trace i=$(i);

trace vStart=$(vStart);

trace vEnd=$(vEnd);

trace vDaysDiff=$(vDaysDiff);

NEXT

Re: How to use For loop

Anonymous — Fri, 08 Dec 2017 11:04:13 GMT

Thanks for your reply !

As I have too many projects nearly 250 and above , So i would not want to to load inline . I have separate field for ProjectId, Startdate, End Date . So I would want to use field rather than going for load in line .

Also want to calculate no. of months between start date and end date .

Using For loop only .

And also is this script for qlikview or qliksense

Kindly help me with this .

Re: How to use For loop

maxgro — Fri, 08 Dec 2017 11:19:58 GMT

I used inline to make some test data, you can replace my inline data with your table data; maybe you have to change the names of the fields.

This works for QlikView and Qlik Sense, the syntax of the script is the same (almost the same); change the table name and field names with yours.

The differnce is this script calculates variables, the script I posted at the beginning of previous post calculates 2 new fields you can use in charts.

for i=0 to NoOfRows('T') -1

LET vStart = Peek('StartDate', $(i), T);

LET vEnd = Peek('EndDate', $(i), T);

LET vDaysDiff = '$(vEnd)' - '$(vStart)';

trace i=$(i);

trace vStart=$(vStart);

trace vEnd=$(vEnd);

trace vDaysDiff=$(vDaysDiff);

NEXT

Re: How to use For loop

Anonymous — Fri, 08 Dec 2017 12:48:48 GMT

Thanks you for reply !

Now in case i use Fields in my inline load ..what will be my

"for i=0 to NoOfRows('T') -1 ?

As of now , I am doing something like this

[Line]:

load

IDprojects,

"StartDate",

EndDate,

For i=0 to NoOfRows('Line') -1

LET vStart = Peek('StartDate', $(i), T);

LET vEnd = Peek('EndDate', $(i), T);

LET vDaysDiff = '$(vEnd)' - '$(vStart)';

trace i=$(i);

trace vStart=$(vStart);

trace vEnd=$(vEnd);

trace vDaysDiff=$(vDaysDiff);

At the end i also want to

calculate the Sum of (difference in days of all projects). Can u plz give entire script if possible .

Thanks In advance