LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: yifeng

Hi,

I have a 3 millions records input file and I want to reduce the number in the output by grouping the records into different time slot.

Each record has a time attched to indicate when the record is being registered in the system. I want to use this time field to do a BETWEEN and AND query to group the records in time slots. eg. I want to filter the records between 15:00:00 and 15:59:59 to the output.

Thanks.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Yifeng,

Good day.

I am not entirely certain I understand where it is you would like to query your data, so I will offer 2 possibilities:

If you are refering to altering your data at the time it is loaded, unfortunately, the input-file type nodes do not at this time have the ability to self-query the data they are loading. If you are loading from a database, you can easily setup a SQL query to accomplish this, but not with the input nodes that load files.
The only way to filter your records when loading from a file, is to load the entire file, then use a Filter node or Aggregate Node to remove or group the records you are not interested in. I will investigate if there is any existing enhancement request similar to this issue, and if not I will file one, as this does seem to be a useful function.

If you are refering to altering and manipulating your data in the graph, and then altering the output from the graph itself, then you have several options.

If you simply wished to filter out records BETWEEN two times, you could use the Filter node and simply

(this is partial pseudocode)
emit * where ((TIMEFIELD < VALUE1) and (TIMEFIELD > VALUE2))

For grouping your data by field values, you would want to use the Aggregate node from the Core library. You can use this to group data by value conditions, and perform manipulations based on those groups.

Information on both of these nodes can be found under your HELP--> Help on Library-->Core menu. (if you are using the Core library. It is the default lib on a new graph).


Regards
Timon Koufopoulos
MDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: yifeng

Hi Timon,

I have tried to use the pseudocode but failed. I think it is because i haven't declare the value to the correct format.

To give you an example say i have the records below and i want to get the first 3 records in the output because their "time" is between "15:00:00" and "16:00:00". what is the pseudocode i can put in Filter to get the output? Thank you.

ApartyNo , BpartyNo, time, Duration of call,Call charge
0011111000, 0290150001, 15:31:37, 77503, 1823600
0011142000, 0290150001, 15:46:44, 40836, 961150
0011230413, 0412721036, 15:22:54, 54100, 8115000
0011230793, 0438460734, 16:44:51, 9244, 1395000

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Yifeng,
Yes, it is true that you have to have your data in the right time/date format to do that comparison. If what you are showing is how your data is listed in your file, it is almost certainly coming in as a "string" type. When you really want a "time" type. Luckily the conversion is simple.


The following code in a Filter node will
1. Update the datatype of the Time field.
2. Emit only the records between the times you have specified.

######
realtime = time('time', "HH:MM:SS")

emit *
where (
realtime > time("15:00:00", "HH:MM:SS") and
realtime < time("15:59:59", "HH:MM:SS)
)
override emit time('time', "HH:MM:SS") as time
######

This example will do what you want. There are a couple ways to make this code a bit more condensed once you are more comfortable. For example you don't really need the extra realtime variable (but it helps when first figuring things out).

##### will do the same thing
emit *
where (
time('time', "HH:MM:SS") > time("15:00:00", "HH:MM:SS") and
time('time', "HH:MM:SS) < time("15:59:59", "HH:MM:SS")
)
override emit time('time', "HH:MM:SS") as time

Either way, you will end up reformatting the 'time' field to a 'time' type, and only emit the records that fall within the range of 15:00:00-15:59:59.

Regards
Timon Koufopoulos
MDA Support.