Jump to content

Extracting data using patterncount


This topic is 2013 days old. Please don't post here. Open a new topic instead.

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

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

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

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.

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.

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

Link to post
Share on other sites
  • 3 weeks later...

 

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.

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?

Link to post
Share on other sites

This topic is 2013 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

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