Similar department in each entity

hi Community,

I have a new requirement where I need to flag similar department from all entities. Using the first 3 letters of their code. below is just a sample of the list:

as you can see TMCI and TMSL has the same department (L3) Laboratory and Pharmacy they also have a similar code the 1st three letters starts with LAB this is what I am going to use to identify similar department across all L1 on my list. I just dont know how to start it. above is basically what it should look like in the module. I already have the module in a mapping module where I have the L1 and L3 spread out

any idea how to start it? any suggestions will be appreciated.

Regards,

Accepted answers

BrunoRodriguez

Hello @Jsdeloria21 ,

If I understand correctly, you just need to extract the 3 initial letters from the code. I would use LEFT and FIND.

Use find to search for the "_" in the code. — FIND("_", CODE)
Use left - 1 to get the three digit code. — LEFT(CODE, FIND -1)
Use find item to map it with the list that utilises that coding.

Hope that helped!

BrunoRodriguez

Hello @Jsdeloria21 ,

Can you try the following?

ISFIRSTOCCURRENCE(CODE,'TMC')

I believe your order is incorrect.

Cheers!

All comments

BrunoRodriguez

Hello @Jsdeloria21 ,

If I understand correctly, you just need to extract the 3 initial letters from the code. I would use LEFT and FIND.

Use find to search for the "_" in the code. — FIND("_", CODE)
Use left - 1 to get the three digit code. — LEFT(CODE, FIND -1)
Use find item to map it with the list that utilises that coding.

Hope that helped!

Jsdeloria21

Hi @BrunoRodriguez ,

Thank you for your feedback. I will re-create the setup on the module. I will try to use the FIND which I have not used before. LEFT on the other hand I have used it before.

Will let you know on this matter. Appreciate your time for this.

Regards,

Jsdeloria21

Hi @BrunoFernandes ,

Using only the 3 letters of the code present on the list. In my example below there is 2 Office of the President 1 which is the child of the TMC Main parent and the other is the child of TMC SL.

Requirement: I need to flag both Department using only the 3 letters of the code as their difference. It will flag bother Office of the President and CEO but only the one under TMC main will have a true on the Boolean. I will do this all through out the list. Whether there is an Office of the president on a new entity TMCI or TMC Iloilo. Then these departments will then be imported to a new list (not clear yet why – but for the sake of requirement). I only use as an example the office of the president there will be other departments like laboratory and pharmacy that will be common to all Entity Hospital on the list. I re-created this on a sample model first before I transfer to the actual model. The Only difference between TMC Main and TMC SL is that departments in TMC SL has an extension of _SL at the end. Same with other entities. Also if you can include the next part where I can compile the common departments to be imported to a new list. I would greatly appreciate it.

Thank you in advance,

Regards,

BrunoRodriguez

Hello @Jsdeloria21 ,

Ok, let's go one step at a time.

My understanding is that your requirement is creating a flat list where all the departments in different hospitals can be aggregated together (i.e., the data for all "Office of the President and CEO" should be aggregated in a single entry).

If the above is correct, the next question is - do you currently have a flat list of departments or do you need to create it?

If you do not have it, It does not really matter which item you are flagging (I would recommend using ISFIRSTOCCURRENCE) as long of the code is the same (for the purpose of creating the list).

The next step once you create the booleans with the ISFIRSTOCCURRENCE is to import the name and code to a new list (i.e., Flat Departments).

If you do already have the flat list, you should create a find item driven by the code and both (Office of the President and CEO) will be identified as an item within the new list.

Using that line item for SUMs would allow you to aggregate the data using SUM.

Hope the above makes sense.

Jsdeloria21

Just in case this might help. This is the view of the List I am using for this

Jsdeloria21

Hi @BrunoRodriguez ,

Do you have some sample of this command ISFIRSTOCCURENCE - I have not really used it before. IF you do that would be amazing.

do you currently have a flat list of departments or do you need to create it?

No I have to create it after I tagged the common department. I will filter the common department and import it to the common Department list. I will follow your approach and let you know how it goes. thanks again.

Regards,

BrunoRodriguez

Hello @Jsdeloria21 ,

Please find below the syntax required for ISFIRSTOCURRENCE:

ISFIRSTOCCURENCE ( Line item containing the code, dimension of the module )

Cool, so just create the list, make the find item and that should be it!

Let me know how it goes!

Jsdeloria21

hi @BrunoRodriguez ,

Am i using it correctly? this one has an error. should I have a line item that is List formatted?

BrunoRodriguez

Hello @Jsdeloria21 ,

Can you try the following?

ISFIRSTOCCURRENCE(CODE,'TMC')

I believe your order is incorrect.

Cheers!

Jsdeloria21

Hi @BrunoRodriguez ,

the order was incorrect. I interchange it and it worked. I also change the list I only use the L3 Department Name instead of the whole list with L1 and L2. it seems to work for some reason with heirarchy it does not seem to work. not sure why. But I appreciate the assistance and I also added another logic to count the code and use it as a parameter to determine whether there is an identical department on the list. Appreciate with this. I really learned a lot here in community I do apologize for sometimes my requirement is not complete. I sometimes get mixed up with all the requirements. Sorry for the confusion.

Regards,

BrunoRodriguez

Hello @Jsdeloria21,

Apologies for my late reply, but I have been busy elsewhere these days.

No need to apologise. I understand that when you come to the community you are confused with the requirements and the data that you have at hand. Otherwise, you would do it yourself :)

Cheers!

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…