How to use For loop

[Anonymous]

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

[maxgro]

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

T:

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

];

F:

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

T:

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

[Anonymous]

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 .

[maxgro]

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

[Anonymous]

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

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 .

Thanks In advance