Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 .
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
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