cancel
Showing results for 
Search instead for 
Did you mean: 

Using the generic sort and filter action block on date time datetime

Former Member
0 Kudos

My BLT uses a SQL query to pull a lot of data (a few thousand records). The data has a SQL timestamp column on it, and the SD and ED are configured to filter to that SQL column. I have a while loop defined so that I can do statistics on small subsections of the data, say the maximum value found on a 15 minute basis.

How do I configure the generic sort and filter action block to filter on date/time? I want to generate sub-queries of the original SQL XML output so I can run statistics on smaller time chunks of data (only 1 SQL querry, but lots of XML subquerries of the SQL querry). I don't need to sort, just filter on the time. Eventually, I need them to be just in the link editor since i'll be making the BLT a bit fancy and dynamic.

Right now I can't figure out how to filter them using the "Configure Object" GUI <b>or</b> in the link editor. I've tried both "text" and "number" datatypes, combinations of parenthesis, quotes, etc. "Number" gives me an error, so I'm sticking with "text". My query always comes out empty, just with the Rowsets/Rowset/Column framework, but no data. I have verified the XML I am passing into it has the expected XML format and data from SQL.

In the link editor, I am passing:

FilterColumn1 = <b>"tTIMESTAMP"</b> (the SQL column name)

FilterType1 = <b>"text"</b>

FilterCondition1 <b>">="</b>

FilterValue1 = <b>1990-01-01T12:00:00</b>

FilterColumn2 = <b>"tTIMESTAMP"</b> (the SQL column name)

FilterType2 = <b>"text"</b>

FilterCondition2 <b>"<"</b>

FilterValue2 = <b>2020-01-01T12:00:00</b>

The help docs don't show how do do this with a date/time. Can anyone help? Thanks!

Jason

Accepted Solutions (0)

Answers (3)

Answers (3)

sufw
Active Participant
0 Kudos

Hi Jason,

if you want to use the Calculated Columns action, you can use one of the built-in functions for the calculation:

datetoseconds( tTIMESTAMP)

Then use a numeric sort/filter action to filter the dataset according to your rules.

However, as you're working with large data sets, I think you will find that the Calculated Columns action gets very slow once you hit 200+ rows. (press F5 in BLS to see execution times). In my experience, using a Repeater and the xMII XML Output actions (i.e. Add Column, Add Row, etc.) will get you better performance.

In this case, I would add a new Column to the Rowset and use a Repeater to add Data Items into the new column. You can use a "Assign Value" type Assignment action to populate the Data Item's value with the result of the above calculation.

Former Member
0 Kudos

Sascha,

Thank you for the warning about using the <b>Calculated Columns</b> action for large data sets. I saw you issued this warning elsewhere in the forums, so I'm guessing its bitten you in the past

I like your method, and was able to successfully add the <b>Column</b> to the Rowset, and add the <b>Repeater</b>, set to repeat on each row (I have the add <b>Column</b> action block pointed to the original <b>SQL Query</b>. However, I am unable to successfully add a data item using the <b>Data Item</b> action. My new column does appear at the beginning of the XML under "Columns", but I can't get that new column and value to be inserted in each row.

The help documentation doesn't show an example of where to point each element, so I can't be sure I'm doing it correctly.

<u>The following questions come to mind:</u>

1. Is it ok to add <b>Column</b> and <b>Data Item</b> to the XML output of a <b>SQL Query</b>?

2. What element do I point the "Illuminator Document" element of <b>Data Item</b> action block?

Thank you,

Jason

sufw
Active Participant
0 Kudos

Hi Jason,

Regarding your questions:

1. Yes. Since xMII transforms the output of any query into the xMII XML format (Rowsets > Rowset > Row), the output of an XML query can be manipulated in the same way as an xMII XML document created using the "xMII XML Output" actions.

2. Normally you would use a Row element as the "target" for the DataItem action (i.e. IlluminatorDocument_0.Output{/Rowsets/Rowset/Row}). However, when placed in a loop, the action seems to only append to the last row, and does so multiple times, leaving you with one tTIMESTAMP node in the last Row for each iteration of the loop. I've never really used the DataItem action, so I wasn't aware of this issue until just now.

In the light of this bug (or lack of understanding on my behalf), may I suggest an alternative which I have used before:

1. Create a new IlluminatorDocument with one column called tTIMEINSECONDS

2. Run a repeater for each Row in the "source" document (i.e. your query output)

3. For each iteration (row in the query output), add a row to the new IlluminatorDocument using the "Row" action. Point this action at IlluminatorDocument.Output{/Rowsets/Rowset}. In the action's link editor, assign datetoseconds( tTIMESTAMP) to the action's tTIMEINSECONDS input property.

4. Once the repeater has finished, use a Union action to merge the two separate XML documents into a single document. Configure the action to use the query output as the first document and IlluminatorDocument.Output as the second document.

5. Finally use a Normalise action to effectively add the tTIMEINSECONDS column to the right of the query output. The output of the Normalise action will now contain what you're after.

The query results and the new document have to have the same number of rows, or else you will get misalignments between the query results and the calculated times. Also, the name of your new column must not be identical to a column in the query results or else the Normalise action will not add it.

Apologies that this is a little more convoluted than using a CalculatedColumns action, but it also gives you a greater degree of flexibility in your calculations.

Regards,

Sascha

Former Member
0 Kudos

Thanks for your resonses. I'm firefighting some issues currently, but will return and digest (and implement) the suggestions in this post as soon as I can. Thanks for understanding,

Jason

Former Member
0 Kudos

Let me clarify this for you. The problem is that xMII's Generic Sort Filter uses XSL transforms to perform the filtering/sorting, and XSL does not allow >= and <= comparisons on text strings.

There's actually a fairly simple workaround that I use. Use the calculated columns action block, and add a column called "DateInRange", of type integer or boolean. For the expression, you can use datetoseconds(tTimestamp) >= datetoseconds(XXX) and datetoseconds(tTimestamp) <= datetoseconds(YYY).

Then, pass this through a generic sort filter and filter where this new column had a value other than zero (I forget whether or not it evaluates to 1 or -1).

You'll find that this technique works fairly well for all kinds of complex filtering expressions.

Best regards,

Rick

Former Member
0 Kudos

Ok, that explains why the "Generic Sort and Filter" doesn't work. Your suggestion should work well:

<b>datetoseconds(tTimestamp) >= datetoseconds(XXX) and datetoseconds(tTimestamp) <= datetoseconds(YYY)</b>

But, unfortunately, for my application, <b>XXX</b> and <b>YYY</b> need to mathematically derrived from a combination of "<b>CurrentItem</b>" from a while loop, and a Transaction variable. It looks like I can only use other columns as non-literal mathmatical operators?

I tried using the link editor, but it appears that this action block is designed to only be edited from the "Configure Object" button?

I might have to go with Ryan's suggestion and build the XML document by hand if the Calculated Column action block can't utilize transaction variables or elements from other action blocks.

Thanks,

Jason

Former Member
0 Kudos

Hi Jason,

It's been awhile since I've tried to filter dates using the Generic Sort Filter. I can't remember if it is possible. What I typically do is use a repeater on my rows and based on a condition like

datecompare(datetoxmlformat(ItemRepeater.Output{/item/tTIMESTAMP}, "yyyy-MM-dd"), Transaction.AsOfDate) > 0

append the row to a new IllumDoc.

Is this helpful?