Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

Thanks In Advance

ravi

4 Replies
maxgro
MVP
MVP

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
Not applicable
Author

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
MVP
MVP

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
Not applicable
Author

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