4 Replies Latest reply: Dec 8, 2017 7:48 AM by RAVI SHINGE

# How to use For loop

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 ProjectStart date(MMDDYYYY)End Date(MMDDYYYY)
1202/02/201520/06/2019
3430/07/201604/05/2018
5626/04/201606/07/2019

Using FOR loop I want to calculate

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

ravi

• ###### Re: How to use For loop

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

T:

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

];

F:

*,

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

T:

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

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

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

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]:

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

NEXT

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 .