on 06-01-2016 3:14 AM
Hello Experts.
In onre of the requirements, I need to find out Date difference between two datetimes entered as DC.
DC Parameter name: START_TIME , Type: Text , Mask: ##:## ##/##/#### , Value: 10:12 05/31/2016
DC Parameter name: END_TIME , Type: Text , Mask: ##:## ##/##/#### , Value: 11:11 05/31/2016
DC Parameter name: TOTAL_TIME , Type: Formula
I have swritten script as: (I have hardcoded ENDTIME as just wantes to see whetehr query works or not)
queryStr = "select datediff(mi , (substring(\'" + START_TIME + "\' , 13 , 4) + \'-\' + substring(\'" + START_TIME + "\' , 7 , 2) + \'-\' + substring(\'" + START_TIME + "\' , 10 , 2) +\'T\' + substring(\'" + START_TIME + "\', 1 , 5) + \':00\' ) , '2016-05-31T11:11:00') AS MINS";
result = executeQuery(queryStr);
TotalTime = result.get("MINS");
exit(TotalTime);
But when I perfrom Data Collect, it gives me an error sayong aht START_TIME is not defined.
What am I doing wrong here? Or is it that formula/scripts will take only numeric fields even if I am passing it as parameter?
Is there any other method to achieve the same?
Regards,
Soham
Soham
Unfortunately, parameters only work for numeric data, not text.
Stuart
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thnaks Stuat.
But I guess as I'm a newbie to ME, I dotn quite get the approach. Can you please explain how can I achieve that?
Suppose I'm storing START_TIME and END_TIME (Both text fields) in DC Group: DC1
In DC Group DC2, I have TOTAL_TIME (Formula), WHat should go in my formula?
Regards,
Soham
You need to query the database to get the start and end times for the DC1 for the current SFC, convert them from strings and subtract. A single query could do this most efficiently. This isn't ideal by any means - the user can cancel a data collection, so you may have to handle missing data too.
Hi Stuart,
For testing purpose, I'm passing following in Formula script:
queryStr = "select TOP 1 ACTUAL from PARAMETRIC_MEASURE where PARA_CONTEXT_GBO =\'";
queryStr = queryStr + SFC_BO+"\' AND MEASURE_NAME = \'START_TIME \' order by PARAMETRIC_MEASURE.TEST_DATE_TIME desc ";
result = executeQuery(queryStr);
actual= result.get("ACTUAL");
exit(actual);
But it is giving me following error:
A mathematical error has occurred; check inputs into formula for valid values (Message 15249)
Can you see what's wrong with the query?
Regards,
Soham
Thaks Stuart.
Yes, exit() only works with Numeric
I was able to find a datetime difference in Minutes between two datetimes START_TIME and END_TIME.
DC Group: TEST1 , DC Parameter: START_TIME , END_TIME
DC Group: TEST2 , DC Parameter: TOTAL_TIME (Type: Formula)
Script looks like this:
queryStr = "SELECT datediff(mi,(substring(S.ACTUAL , 13 , 4) + \'-\' + substring(S.ACTUAL , 7 , 2) + \'-\' + substring(S.ACTUAL , 10 , 2)+\'T\' + substring(S.ACTUAL , 1 , 5) + \':00\' ) , ( substring(E.ACTUAL , 13 , 4) + \'-\' + substring(E.ACTUAL , 7 , 2) + \'-\' + substring(E.ACTUAL , 10 , 2)+\'T\' + substring(E.ACTUAL , 1 , 5) + \':00\' )) as MINS from (SELECT TOP 1 ACTUAL from PARAMETRIC_MEASURE where PARA_CONTEXT_GBO = \'";
queryStr = queryStr + SFC_BO + "\' AND MEASURE_NAME = \'START_TIME\') S , (SELECT TOP 1 ACTUAL from PARAMETRIC_MEASURE where PARA_CONTEXT_GBO = \'";
queryStr = queryStr + SFC_BO + "\' AND MEASURE_NAME = \'END_TIME\') E";
result = executeQuery(queryStr);
mins = result.get("MINS");
exit(mins);
Once I open Data Ollect TEST2 in POD and save it, I am able to see difference calculated and stored in TOTAL_TIME. I can look at it in Data Collection Results Report.
Thaks a lot for the help. Your inputs made it possible
Regards,
Soham
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.