15.7 Take out different types of characters


The following data table stores multiple types of attendance records of employees on a certain day. For example, A means late arrival, and the number after it represents how long he/she is late; B means early leave, and the number after it represents how long he/she leaves early; C means asking for leave, the number after it represents how long he/she asks for leave, and so on. Now we want to summarize the attendance situation, and then count the sum of the numbers after each letter, and finally fill in the results in the black box.


Enter in cell B4:

1 =‘A1:G1’.conj()
2 =create(Type,Value)
3 =A1.(~.words@wp().run(if(#%2==1,A2.record([~,number(~[1])]))))
4 =A2.groups(Type;sum(Value):Total)

A2: Create a table sequence having two columns: Type and Value.
A3: Loop through the string in each cell, and split the string into words. The option @w means that the string will be thoroughly split in such a way that the Chinese characters/symbols are split into single character, and the English words/numbers are split into single word; the option p means that the beginning of the data will be recognized as a numeric value or a date according to the data type, and split into a whole. After splitting, loop through the split sequence, if the current member’s number is odd, take the current member and its next member and convert them into a value to form a row of data and save it in A2
A4: Group A2 by Type and calculate the sum of Value

esProc Desktop and Excel Processing
15.6 Parse and extract dates
15.8 Take out words