Logging Boolean Tag

pturmel · August 1, 2024, 7:26pm

Bind the named query with parameters to your Perspective Table's props.data (as described above). In the binding configuration, select Dataset return format. Your table will show the raw results (with long integer milliseconds for t_stamp and t_next).

Once you see the raw results, there should be an icon next to props.data that opens the dataset editor. From there, click the "Copy to Clipboard" button.

Then reply here on the forum, paste that CSV content, re-highlight in the forum comment editor, then click the "Preformatted Text" button. The one that looks like this: </>

thomas_wallace · August 1, 2024, 7:33pm

Im not seeing the data set return. I know I should know this. Thank you being patient.

pturmel · August 1, 2024, 7:36pm

You haven't provided bindings within the NQ binding for its parameters. For which I instructed you to create view custom parameters. (Each of those with expression bindings.)

Do not use return format "auto". Use "dataset" return format.

thomas_wallace · August 1, 2024, 7:50pm

@pturmel

pturmel · August 1, 2024, 7:53pm

You are trying to add a custom prop to the view's root container. Don't. Define it at the view itself. (Click on the name of the view in the designer tree, then scroll to the "Custom" section of its properties.)
Do the end property first, because start needs it.
Name the property correctly before opening the binding dialog.

thomas_wallace · August 1, 2024, 7:59pm

Didnt even realize i was on the root container.

thomas_wallace · August 1, 2024, 8:01pm

pturmel · August 1, 2024, 8:02pm

Please fix key and key_1 to end and start.

thomas_wallace · August 1, 2024, 8:05pm

ok fixed. So now in the data parameters change them to point to the customs?

thomas_wallace · August 1, 2024, 8:11pm

thomas_wallace · August 1, 2024, 8:18pm

"#NAMES"
"chargetime_ndx","AGV10","t_stamp","t_next"
"#TYPES"
"I","I","date","date"
"#ROWS","53"
"24","0","2024-07-31 01:59:38.377","2024-07-31 08:11:05.617"
"25","1","2024-07-31 08:11:05.617","2024-07-31 08:11:43.787"
"26","0","2024-07-31 08:11:43.787","2024-07-31 08:19:34.507"
"27","1","2024-07-31 08:19:34.507","2024-07-31 08:20:05.537"
"28","0","2024-07-31 08:20:05.537","2024-07-31 08:20:35.573"
"29","1","2024-07-31 08:20:35.573","2024-07-31 08:23:20.950"
"30","0","2024-07-31 08:23:20.950","2024-07-31 08:23:40.973"
"31","1","2024-07-31 08:23:40.973","2024-07-31 08:29:07.783"
"32","0","2024-07-31 08:29:07.783","2024-07-31 08:29:23.807"
"33","1","2024-07-31 08:29:23.807","2024-07-31 08:34:03.357"
"34","0","2024-07-31 08:34:03.357","2024-07-31 08:35:09.557"
"35","1","2024-07-31 08:35:09.557","2024-07-31 08:40:33.230"
"36","0","2024-07-31 08:40:33.230","2024-07-31 08:40:48.247"
"37","1","2024-07-31 08:40:48.247","2024-07-31 08:47:50.027"
"38","0","2024-07-31 08:47:50.027","2024-07-31 08:48:05.070"
"39","1","2024-07-31 08:48:05.070","2024-07-31 08:48:36.110"
"40","0","2024-07-31 08:48:36.110","2024-07-31 08:54:56.517"
"41","1","2024-07-31 08:54:56.517","2024-07-31 08:55:34.563"
"42","0","2024-07-31 08:55:34.563","2024-07-31 10:02:51.380"
"43","1","2024-07-31 10:02:51.380","2024-07-31 10:14:33.647"
"44","0","2024-07-31 10:14:33.647","2024-07-31 12:14:58.550"
"45","1","2024-07-31 12:14:58.550","2024-07-31 12:16:55.507"
"46","0","2024-07-31 12:16:55.507","2024-07-31 12:37:05.820"
"47","1","2024-07-31 12:37:05.820","2024-07-31 12:37:22.967"
"48","0","2024-07-31 12:37:22.967","2024-07-31 12:48:53.967"
"49","1","2024-07-31 12:48:53.967","2024-07-31 12:53:42.037"
"50","0","2024-07-31 12:53:42.037","2024-07-31 12:54:06.063"
"51","1","2024-07-31 12:54:06.063","2024-07-31 12:58:21.620"
"52","0","2024-07-31 12:58:21.620","2024-07-31 12:58:48.647"
"53","1","2024-07-31 12:58:48.647","2024-07-31 12:59:35.700"
"54","0","2024-07-31 12:59:35.700","2024-07-31 12:59:59.773"
"55","1","2024-07-31 12:59:59.773","2024-07-31 13:03:04.630"
"56","0","2024-07-31 13:03:04.630","2024-07-31 13:03:28.687"
"57","1","2024-07-31 13:03:28.687","2024-07-31 13:03:35.850"
"58","0","2024-07-31 13:03:35.850","2024-07-31 13:03:55.870"
"59","1","2024-07-31 13:03:55.870","2024-07-31 13:06:35.383"
"60","0","2024-07-31 13:06:35.383","2024-07-31 13:06:49.397"
"61","1","2024-07-31 13:06:49.397","2024-07-31 13:12:45.470"
"62","0","2024-07-31 13:12:45.470","2024-07-31 13:12:59.533"
"63","1","2024-07-31 13:12:59.533","2024-07-31 13:18:00.960"
"64","0","2024-07-31 13:18:00.960","2024-07-31 17:20:17.993"
"65","1","2024-07-31 17:20:17.993","2024-07-31 17:20:32.007"
"66","0","2024-07-31 17:20:32.007","2024-07-31 17:49:51.453"
"67","1","2024-07-31 17:49:51.453","2024-07-31 17:51:34.747"
"68","0","2024-07-31 17:51:34.747","2024-07-31 17:51:59.867"
"69","1","2024-07-31 17:51:59.867","2024-07-31 18:02:22.937"
"70","0","2024-07-31 18:02:22.937","2024-07-31 18:08:47.757"
"71","1","2024-07-31 18:08:47.757","2024-07-31 18:15:36.600"
"72","0","2024-07-31 18:15:36.600","2024-07-31 18:15:51.617"
"73","1","2024-07-31 18:15:51.617","2024-07-31 18:26:04.037"
"74","0","2024-07-31 18:26:04.037","2024-07-31 23:37:56.060"
"75","1","2024-07-31 23:37:56.060","2024-07-31 23:38:08.073"
"76","0","2024-07-31 23:38:08.073",

@pturmel

pturmel · August 1, 2024, 8:27pm

Very good. Notice that the first row overlaps yesterday's start of day, and the last row has a null endpoint. Time to account for that by wrapping another operation around what you have so far. New SQL for your NQ:

SELECT AGV10 As Charging,
	CASE WHEN t_stamp < :startts THEN :startts ELSE t_stamp END AS clipped_t_stamp,
	COALESCE(t_next, :endts) AS clipped_t_next
FROM (
	SELECT *,
		LEAD(t_stamp) OVER (ORDER BY t_stamp) AS t_next
	FROM ChargeTime
	WHERE
		t_stamp >= (
			SELECT TOP 1 t_stamp
			FROM ChargeTime
			WHERE t_stamp < :startts
			ORDER BY t_stamp DESC
		)
		AND t_stamp < :endts
) clipped
ORDER BY clipped_t_stamp

thomas_wallace · August 1, 2024, 8:34pm

New querie. How does this tie into the perspective table?

pturmel · August 1, 2024, 8:37pm

I expected you to just change the prior NQ, but you can just point the table binding at this instead.

thomas_wallace · August 1, 2024, 8:37pm

Ohhh. Ill change the original. Just misunderstood you

pturmel · August 1, 2024, 8:40pm

Share the new dataset...

thomas_wallace · August 1, 2024, 8:43pm

Computer crashed...... ill have to re-do the view. We can pick back up another day.

pturmel · August 1, 2024, 8:44pm

When you start back up, here's the next step:

SELECT Charging,
	:startts AS PeriodStart,
	0.001 * SUM(DATEDIFF(ms, clipped_t_stamp, clipped_t_next)) AS Seconds
FROM (
	SELECT AGV10 As Charging,
		CASE WHEN t_stamp < :startts THEN :startts ELSE t_stamp END AS clipped_t_stamp,
		COALESCE(t_next, :endts) AS clipped_t_next
	FROM (
		SELECT *,
			LEAD(t_stamp) OVER (ORDER BY t_stamp) AS t_next
		FROM ChargeTime
		WHERE
			t_stamp >= (
				SELECT TOP 1 t_stamp
				FROM ChargeTime
				WHERE t_stamp < :startts
				ORDER BY t_stamp DESC
			)
			AND t_stamp < :endts
	) clipped
) counted
GROUP BY Charging
ORDER BY Charging

thomas_wallace · August 1, 2024, 8:47pm

"#NAMES"
"Charging","clipped_t_stamp","clipped_t_next"
"#TYPES"
"I","date","date"
"#ROWS","53"
"0","2024-07-31 07:00:00.000","2024-07-31 08:11:05.616"
"1","2024-07-31 08:11:05.616","2024-07-31 08:11:43.786"
"0","2024-07-31 08:11:43.786","2024-07-31 08:19:34.506"
"1","2024-07-31 08:19:34.506","2024-07-31 08:20:05.536"
"0","2024-07-31 08:20:05.536","2024-07-31 08:20:35.573"
"1","2024-07-31 08:20:35.573","2024-07-31 08:23:20.950"
"0","2024-07-31 08:23:20.950","2024-07-31 08:23:40.973"
"1","2024-07-31 08:23:40.973","2024-07-31 08:29:07.783"
"0","2024-07-31 08:29:07.783","2024-07-31 08:29:23.806"
"1","2024-07-31 08:29:23.806","2024-07-31 08:34:03.356"
"0","2024-07-31 08:34:03.356","2024-07-31 08:35:09.556"
"1","2024-07-31 08:35:09.556","2024-07-31 08:40:33.230"
"0","2024-07-31 08:40:33.230","2024-07-31 08:40:48.246"
"1","2024-07-31 08:40:48.246","2024-07-31 08:47:50.026"
"0","2024-07-31 08:47:50.026","2024-07-31 08:48:05.070"
"1","2024-07-31 08:48:05.070","2024-07-31 08:48:36.110"
"0","2024-07-31 08:48:36.110","2024-07-31 08:54:56.516"
"1","2024-07-31 08:54:56.516","2024-07-31 08:55:34.563"
"0","2024-07-31 08:55:34.563","2024-07-31 10:02:51.380"
"1","2024-07-31 10:02:51.380","2024-07-31 10:14:33.646"
"0","2024-07-31 10:14:33.646","2024-07-31 12:14:58.550"
"1","2024-07-31 12:14:58.550","2024-07-31 12:16:55.506"
"0","2024-07-31 12:16:55.506","2024-07-31 12:37:05.820"
"1","2024-07-31 12:37:05.820","2024-07-31 12:37:22.966"
"0","2024-07-31 12:37:22.966","2024-07-31 12:48:53.966"
"1","2024-07-31 12:48:53.966","2024-07-31 12:53:42.036"
"0","2024-07-31 12:53:42.036","2024-07-31 12:54:06.063"
"1","2024-07-31 12:54:06.063","2024-07-31 12:58:21.620"
"0","2024-07-31 12:58:21.620","2024-07-31 12:58:48.646"
"1","2024-07-31 12:58:48.646","2024-07-31 12:59:35.700"
"0","2024-07-31 12:59:35.700","2024-07-31 12:59:59.773"
"1","2024-07-31 12:59:59.773","2024-07-31 13:03:04.630"
"0","2024-07-31 13:03:04.630","2024-07-31 13:03:28.686"
"1","2024-07-31 13:03:28.686","2024-07-31 13:03:35.850"
"0","2024-07-31 13:03:35.850","2024-07-31 13:03:55.870"
"1","2024-07-31 13:03:55.870","2024-07-31 13:06:35.383"
"0","2024-07-31 13:06:35.383","2024-07-31 13:06:49.396"
"1","2024-07-31 13:06:49.396","2024-07-31 13:12:45.470"
"0","2024-07-31 13:12:45.470","2024-07-31 13:12:59.533"
"1","2024-07-31 13:12:59.533","2024-07-31 13:18:00.960"
"0","2024-07-31 13:18:00.960","2024-07-31 17:20:17.993"
"1","2024-07-31 17:20:17.993","2024-07-31 17:20:32.006"
"0","2024-07-31 17:20:32.006","2024-07-31 17:49:51.453"
"1","2024-07-31 17:49:51.453","2024-07-31 17:51:34.746"
"0","2024-07-31 17:51:34.746","2024-07-31 17:51:59.866"
"1","2024-07-31 17:51:59.866","2024-07-31 18:02:22.936"
"0","2024-07-31 18:02:22.936","2024-07-31 18:08:47.756"
"1","2024-07-31 18:08:47.756","2024-07-31 18:15:36.600"
"0","2024-07-31 18:15:36.600","2024-07-31 18:15:51.616"
"1","2024-07-31 18:15:51.616","2024-07-31 18:26:04.036"
"0","2024-07-31 18:26:04.036","2024-07-31 23:37:56.060"
"1","2024-07-31 23:37:56.060","2024-07-31 23:38:08.073"
"0","2024-07-31 23:38:08.073","2024-08-01 07:00:00.000"



New dataset. Havent done your last one yet

thomas_wallace · August 1, 2024, 8:53pm

Thats getting an error.....Incorrect syntax near ')'.")

@pturmel