topic Subfield() function Problem ? in QlikView

Subfield() function Problem ?

ganeshreddy — Mon, 11 May 2015 11:18:02 GMT

Hi All,

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:

https://community.qlik.com/docs/DOC-6998

https://community.qlik.com/message/589691#589691

jagan rbecher here they are talking about splitting, how can i resolve this problem by using splitting in my scenario. Please find attached QVW and link for my query.

https://community.qlik.com/message/762999#762999

Cheers,

Ganesh.

Re: Subfield() function Problem ?

Peter_Cammaert — Mon, 11 May 2015 11:47:32 GMT

Try to serialize all your time registrations for every Employee, just like in the example for Emp_ID = 107. QlikView works better with fewer string manipulations and more pure row data operations..

You may need to tune a few things inbetween (like optionally throwing out all text values)

Then for every employee and every single day, find the first and last time registration and calculate the difference.

Best,

Peter

Re: Subfield() function Problem ?

ganeshreddy — Mon, 11 May 2015 12:39:54 GMT

Hi Peter,

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‌

Cheers,

Ganesh.

Re: Subfield() function Problem ?

Peter_Cammaert — Mon, 11 May 2015 15:31:34 GMT

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:

Table2:

NOCONCATENATE

LOAD RowNo() AS RID, Date, [EMP ID], subfield(Meal_Times, '|') AS Event

RESIDENT EMP_TIME;

Table3:

NOCONCATENATE

LOAD RID, Date, [EMP ID], Event,

subfield(Event, '-', 1) AS Start,

subfield(Event, '-', 2) AS Stop

RESIDENT Table2

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?

Peter