Extracting data using patterncount

[Aussie John]

Hi Have a field "roomname", each with a roomarea. For example

tutorial 1

tutorial 2

Classroom 

Lecture theatre

Store 

office

 

What I would like to do is create a calculation which shows the area when it is a particular "type" of room.

For instance to create a field  "teaching space area", I could use

Case(

patterncount(roomname,"tutor",

or patterncount(roomname,"class",

or patterncount(roomname,"lecture",

Roomarea)

 

Then I can do a totals , counts and size breakdowns on these spaces.

 

In an inherited spreadsheet the "list of names" might be quite extensive and never consistent and it would be a lot less cumbersome to offer a list of names in a custom function. eg

Type("tutor","class","lecture") or maybe reference a field with a list in it. Unfortunately I don't seem to be able to do this. Any ideas? Thanks

[Aussie John]

I have tried a custom function of;

type(field;patternlist;area) as follows;

Let([

 

$counter=Case($counter<1;1;$counter+1);

VC=ValueCount(patternlist);

GV=GetValue(patternlist;$V);

result =Case(PatternCount(field;GV)>0;area;)];

 

Case(IsEmpty(result) and $V≤VC; type(field;patternlist;area);result)

)

The calculation field is;

type ( Room::Room Name ;"office¶admin"; Room::Room Area )

 

I tried a few other things but either it only finds the first line or gets into a loop. This example gives no result.

[webko]

Depending how many variations there are...

 

I will often create a value list based on the field - this gives me a list of the unique versions.

 

Then I'll inspect the list for the variations - let's say there is

 

Office

Office Space

Offffffice

 

I'll then Find for "Offffffice" and do a Replace with "Office" - this variant then drops off the list, as it no longer exists in the target field. Repeat until the data is cleaned up...

 

Then use the value list for data entry thereafter.

 

And now you have values you can use per above.

[Aussie John]

Depending how many variations there are...

 

I will often create a value list based on the field - this gives me a list of the unique versions.

 

Then I'll inspect the list for the variations - let's say there is

 

Office

Office Space

Offffffice

 

I'll then Find for "Offffffice" and do a Replace with "Office" - this variant then drops off the list, as it no longer exists in the target field. Repeat until the data is cleaned up...

 

Then use the value list for data entry thereafter.

 

And now you have values you can use per above.

Yes I have done this too. but I need a calculation as there are so many records

[Aussie John]

 

I have tried a custom function of;

type(field;patternlist;area) as follows;

Let([

 

$counter=Case($counter<1;1;$counter+1);

VC=ValueCount(patternlist);

GV=GetValue(patternlist;$V);

result =Case(PatternCount(field;GV)>0;area;)];

 

Case(IsEmpty(result) and $V≤VC; type(field;patternlist;area);result)

)

The calculation field is;

type ( Room::Room Name ;"office¶admin"; Room::Room Area )

 

I tried a few other things but either it only finds the first line or gets into a loop. This example gives no result.

 

I thought this might be a simple question.

[eos]

I thought this might be a simple question.

 

I wanted to suggest that you clean up your data, then use that to create a RoomTypes table, where you can flag a type (“Teaching”, “Admin”, ”Leisure“) or a generic calculation flag, and use that in the other table, as in 

Case ( RoomTypes::type = "Teaching" /* or: RoomTypes::calculateArea */ ; yourAreaCalculation )

That allows you to easily create new room types and simply flag them, correct spelling mistakes, change calculations by setting/unsetting flags etc.

 

A CF you could create is

// PatternFoundFromList ( field ; patternList ) ;
Let ( [
  found = PatternCount ( field ; GetValue ( patternList ; 1 ) ) ;
  remainder = ValueCount ( patternList ) - 1
  ] ;
  Case ( 
    not found and remainder ; 
    PatternFoundFromList ( field ; MiddleValues ( patternList ;  2 ; remainder ) ) ;
    found
  )
)

then use it with

Case ( 
  PatternFound ( Room::Room Name ; "office¶admin" ) ;
  yourAreaCalculation
)

Note the separation of concerns; the CF does one thing (check for a pattern), not two.

 

P.S.: Please don't quote entire postings.

[comment]

I thought this might be a simple question.

 

It may be simple, but it's not quite clear.

 

 

Hint: you should never ask how to do X using Y.  Lookup "XY problem".

 

 

Now, if I am guessing correctly what your real purpose is here, then a custom function - which hard-codes data about room types - is not the correct tool for this problem. (I meant to add more, but I see that eos already has most of it).

[Aussie John]

 

P.S.: Please don't quote entire postings.

Good advice, I was in a bit of a hurry but will remember next time. Also thanks for the code as it was helpful.

[Aussie John]

 

Now, if I am guessing correctly what your real purpose is here, then a custom function - which hard-codes data about room types - is not the correct tool for this problem. (I meant to add more, but I see that eos already has most of it).

I only hard coded the data because I could not get a list in a field to work, which I subsequently have. The recursive function ended up working quite well but was very slow to keep live.

[LaRetta]

Hint: you should never ask how to do X using Y.  Lookup "XY problem".

 

Nice find! 

[the Otter]

Aussie John,

 

Ignoring the data cleanup for a moment, how about:

If (

   PatternCount (

      ¶ & List ( "tutor" ; "class" ; "lecture" ) & ¶ ;

      ¶ & roomname & ¶

   ) //end PatternCount

   ; Roomarea

) //end If

Obviously this could be simplified with a custom function, but that’s basically what you’re looking for, right?