In my transformations i am using subfield() function to get First meal in/out times, second meal in/out times from a string(Meal_times).
it is working fine for small set of data, while i am using with real time (millions of records) it is not working, it is taking ages to load and also it seems to be never ending load. I have gone through few community links but i didn't understood them well ref:
I have tried all possibilities which you have mentioned, but no use. Here if i use distinct key word it is working fine but i am loosing records. Can you give me a possible solution. i just referred this link The Secret Life of SubField
What I meant wass something to improve performance (and I guess you didn't try this yet, or performance wouldn't be an issue anymore). To serialize the information, you can try omething like this. The script snippet loads from the INLINE table fields:
LOAD RowNo() AS RID, Date, [EMP ID], subfield(Meal_Times, '|') AS Event
LOAD RID, Date, [EMP ID], Event,
subfield(Event, '-', 1) AS Start,
subfield(Event, '-', 2) AS Stop
ORDER BY Date, [EMP ID], RID;;
DROP Table Table2;
DROP Table EMP_TIME;
You now have a table with serialized events. Time periods will be split in a start and stop time. RID is used to restore the original order, otherwise you should use StartTime.
Now the important questions: what are you trying to figure out, and what defines "a meal"? The break between Stop and Start times? Something else as well?