Jump to content
Sign in to follow this  
Aussie John

Extracting data using patterncount

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

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

 

Nice find! 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.