2 footers - Similar Data

Steve Caiels_21881 said:

Hi Vaughn,

I think the easiest way might be to create two models that both create the same table structure.  One for 'empty' bookings and one for full.  You can export each table as a fixed width text file and bring them both back in to a third Monach Classic session to append them, if needed.

Monarch Data Prep Studio could do this in a single pass as you can effectively have two models and an Append in a single workspace.

Assuming you would have a fairly limited number of duplicate bookings, I believe it is possible to do in a single pass of Monarch Classic using a Multi Column Region trick that, to my knowledge, was first described by Olly Bond on the previous version of these forums.

Here goes!
Based on the attached 'forumSample.txt'

 - Create a Multi Column Region out to the right of the report. Create one column for each potential booking per timeslot.  I'm assuming there could be no more than 5 bookings in a single timeslot for the example.  I believe the maximum would be 40 in Version 13.

 

- Create a 5-line Page Header starting on the Page line

- Create a two-line detail on the Time Slot line
Ignore patient details for now, so the 2^nd line will remain blank
Extract Time slot and Slot Status

At this stage, the table will have duplicated and redundant rows as the MCR creates one detail for each column of the MCR.
- Create a Sort field in the table to group the results of the MCR in a logical order. 
Expression : str(Page(),3,0,"0") + "-" + Str(line(),3,0,"0") + "-" + Str(column(),3,0,"0")
You should now have 5 duplicated row for each time slot - enough room for 5 bookings in the next stage.

- Edit the detail template and add the patient fields.  The 2^nd sample line is blank, so just line up the fields for now.
Name the fields "AllPatients" and "AllAilments" and set the advanced end field options to 2 blank field values.
It should look like this.

- Create a Calculated field called Patient

- Repeat for Ailments

- Create a filter

- Hide the unwanted columns

 I have attached the model file, but had to rename it to Forum Sammple.dmod.txt to attach it to the forum.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 04-23-2019 02:24 PM
From: Vaughn Mitchell
Subject: 2 footers - Similar Data

I see what you are saying. Is doing a "not" possible in monarch 13?

EDIT: Just saw your edit.

------------------------------
Vaughn Mitchell
Data Analyst
------------------------------

Original Message:
Sent: 04-23-2019 01:20 PM
From: Austin Perkins
Subject: 2 footers - Similar Data

Vaughn,

There are likely better options out there but it can be done using this method. I have no doubt someone else is going to reply with a really simple fix but I was able to do what you are wanting by doing the following:

Create a detail standard trap on the patient row but put a "not" period to remove the doctor row or put "is blank" as the first character to remove the doctor rows. Also put a "not" T to get rid of the Total rows and finally put a "not" - to get rid of the time records. This should leave you with several blank rows and your patient detail rows.

Create a standard append template on the doctor row using "is not blank" in the first character.

Create a standard append template on the time slow row using the dash.

Your end result will have several blank rows but you can put an auto filter to remove these in the table. Let me know if I need to explain further. Also feel free to send me a non-PHI file and I can send you back a model.

EDIT: The above response was based on a small file I recreated versus copying out what you had sent. I have since created a file identical to what you provided and have new issues. The best I can come up with ends up with a lot of duplicated rows and isn't overly useful. I've attached the TXT file and example DMOD so others can attempt to correct. link to files: https://ufile.io/pepi4zgz

Regards,

------------------------------
Austin Perkins, CPA
Managing Consultant
BKD, LLP
Louisville, Kentucky
502-963-0812

Original Message:
Sent: 04-23-2019 12:23 PM
From: Vaughn Mitchell
Subject: 2 footers - Similar Data

I am having problems getting both patients on the list for the 7:15 - 7:30 Time slot. (Tom and Jerry) My model works great when there is just one but, I am not sure how to get both on the report.


I don't think I can make the patients the detail line because then it will only grab the booked time slots and  I miss the Available and Unavailable time slots.

Here is what the report looks like.

I would like the output to look like this:

Start Time	End Time	Status	Patient Name	Visit Reason	Age	Account #	Provider
11:30	13:15	Available					Dr.Bug
13:15	13:30	Unavailable					Dr.Bug
13:30	13:45	Unavailable					Dr.Bug
13:45	14:00	Unavailable					Dr.Bug
14:00	14:15	Unavailable					Dr.Bug
14:15	14:30	Unavailable					Dr.Bug
14:30	15:15	Unavailable					Dr.Bug
15:15	15:30	Avaliable					Dr.Bug
15:30	15:45	Unavailable					Dr.Bug
15:45	16:00	Unavailable					Dr.Bug
16:00	16:15	Unavailable					Dr.Bug
07:00	07:15	Booked	Morgan,Dexter	Cooties	41	1111111111	Dr.Doom
07:15	07:30	Overbooked	Jerry	Rash	9	2222222222	Dr.Doom
07:15	07:30	Overbooked	Tom	Headache	20	3333333333	Dr.Doom
07:30	07:45	Booked	Pickachu	Back Pain	27	4444444444	Dr.Doom
07:45	08:00	Booked	Woody	Cough	18	5555555555	Dr.Doom
08:00	08:15	Booked	Buzz	Runny Nose	19	6666666666	Dr.Doom
08:15	08:30	Unavailable					Dr.Doom
08:30	08:45	Unavailable					Dr.Doom
08:45	09:00	Unavailable					Dr.Doom

 

 

                                                                               Schedule List                     Page: 1

                                                  By Doctor                       Date: Today

                                                Date 04/23/2019                                                        User: Mitchell

 

Dr.Bug                                

     Total Appts:   11 

 

 

 11:30    -  13:15   Available                                 

 

 13:15    -  13:30   Unavailable                                    

 

 13:30    -  13:45   Unavailable                                    

 

 13:45    -  14:00   Unavailable                                    

 

 14:00    -  14:15   Unavailable                                    

 

 14:15    -  14:30   Unavailable                                    

 

 14:30    -  15:15   Unavailable                                    

 

 15:15    -  15:30   Avaliable                                   

 

 15:30    -  15:45   Unavailable                                    

 

 15:45    -  16:00   Unavailable                                    

 

 16:00    -  16:15   Unavailable                                    

                                                                               Schedule List                     Page: 2

                                                By Doctor                           Date: Today

                                                Date 04/23/2019                                        User: Mitchell

Dr. Doom                            

     Total Appts:   8 

                     

 07:00    -  07:15   Booked         1                              

 

 Morgan,Dexter                 Cooties                               41   01/31/2019 1111111111   ABC1111111111

                              

 07:15    -  07:30   Overbooked     1                               

 

 Jerry                                          Rash                               9    01/28/2019 2222222222   ABC2222222222

                              

 Tom                                             Headache                     20   11/05/2019 3333333333   ABC3333333333

                              

 07:30    -  07:45   Booked         1                               

 

 Pickachu                   Back Pain                27   07/30/2019 4444444444   ABC4444444444

                              

 07:45    -  08:00   Booked         1                                

 

 Woody                       Cough                           18   11/02/2019 5555555555   ABC5555555555

                              

 08:00    -  08:15   Booked         1                               

 

 Buzz                                            Runny Nose                       19   12/26/2019 6666666666   ABC6666666666

                              

 08:15    -  08:30   Unavailable                                    

 

 08:30    -  08:45   Unavailable                                    

 

 08:45    -  09:00   Unavailable                                    

------------------------------
Vaughn Mitchell
Data Analyst

------------------------------
#BestAnswer"

See, told you someone would blow my thoughts up.

Steve - awesome solution, thanks for sharing!

------------------------------
Austin Perkins, CPA
Managing Consultant
BKD, LLP
Louisville, Kentucky
502-963-0812
------------------------------
-------------------------------------------
Original Message:
Sent: 04-24-2019 10:01 AM
From: Steve Caiels
Subject: 2 footers - Similar Data

Hi Vaughn,

I think the easiest way might be to create two models that both create the same table structure.  One for 'empty' bookings and one for full.  You can export each table as a fixed width text file and bring them both back in to a third Monach Classic session to append them, if needed.

Monarch Data Prep Studio could do this in a single pass as you can effectively have two models and an Append in a single workspace.

Assuming you would have a fairly limited number of duplicate bookings, I believe it is possible to do in a single pass of Monarch Classic using a Multi Column Region trick that, to my knowledge, was first described by Olly Bond on the previous version of these forums.

Here goes!
Based on the attached 'forumSample.txt'

 - Create a Multi Column Region out to the right of the report. Create one column for each potential booking per timeslot.  I'm assuming there could be no more than 5 bookings in a single timeslot for the example.  I believe the maximum would be 40 in Version 13.

 

- Create a 5-line Page Header starting on the Page line

- Create a two-line detail on the Time Slot line
Ignore patient details for now, so the 2^nd line will remain blank
Extract Time slot and Slot Status

At this stage, the table will have duplicated and redundant rows as the MCR creates one detail for each column of the MCR.
- Create a Sort field in the table to group the results of the MCR in a logical order.
Expression : str(Page(),3,0,"0") + "-" + Str(line(),3,0,"0") + "-" + Str(column(),3,0,"0")
You should now have 5 duplicated row for each time slot - enough room for 5 bookings in the next stage.

- Edit the detail template and add the patient fields.  The 2^nd sample line is blank, so just line up the fields for now.
Name the fields "AllPatients" and "AllAilments" and set the advanced end field options to 2 blank field values.
It should look like this.

- Create a Calculated field called Patient

- Repeat for Ailments

- Create a filter

- Hide the unwanted columns

 I have attached the model file, but had to rename it to Forum Sammple.dmod.txt to attach it to the forum.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------

Original Message:
Sent: 04-23-2019 02:24 PM
From: Vaughn Mitchell
Subject: 2 footers - Similar Data

I see what you are saying. Is doing a "not" possible in monarch 13?

EDIT: Just saw your edit.

------------------------------
Vaughn Mitchell
Data Analyst

Original Message:
Sent: 04-23-2019 01:20 PM
From: Austin Perkins
Subject: 2 footers - Similar Data

Vaughn,

There are likely better options out there but it can be done using this method. I have no doubt someone else is going to reply with a really simple fix but I was able to do what you are wanting by doing the following:

Create a detail standard trap on the patient row but put a "not" period to remove the doctor row or put "is blank" as the first character to remove the doctor rows. Also put a "not" T to get rid of the Total rows and finally put a "not" - to get rid of the time records. This should leave you with several blank rows and your patient detail rows.

Create a standard append template on the doctor row using "is not blank" in the first character.

Create a standard append template on the time slow row using the dash.

Your end result will have several blank rows but you can put an auto filter to remove these in the table. Let me know if I need to explain further. Also feel free to send me a non-PHI file and I can send you back a model.

EDIT: The above response was based on a small file I recreated versus copying out what you had sent. I have since created a file identical to what you provided and have new issues. The best I can come up with ends up with a lot of duplicated rows and isn't overly useful. I've attached the TXT file and example DMOD so others can attempt to correct. link to files: https://ufile.io/pepi4zgz

Regards,

------------------------------
Austin Perkins, CPA
Managing Consultant
BKD, LLP
Louisville, Kentucky
502-963-0812

Original Message:
Sent: 04-23-2019 12:23 PM
From: Vaughn Mitchell
Subject: 2 footers - Similar Data

I am having problems getting both patients on the list for the 7:15 - 7:30 Time slot. (Tom and Jerry) My model works great when there is just one but, I am not sure how to get both on the report.


I don't think I can make the patients the detail line because then it will only grab the booked time slots and  I miss the Available and Unavailable time slots.

Here is what the report looks like.

I would like the output to look like this:

Start Time	End Time	Status	Patient Name	Visit Reason	Age	Account #	Provider
11:30	13:15	Available					Dr.Bug
13:15	13:30	Unavailable					Dr.Bug
13:30	13:45	Unavailable					Dr.Bug
13:45	14:00	Unavailable					Dr.Bug
14:00	14:15	Unavailable					Dr.Bug
14:15	14:30	Unavailable					Dr.Bug
14:30	15:15	Unavailable					Dr.Bug
15:15	15:30	Avaliable					Dr.Bug
15:30	15:45	Unavailable					Dr.Bug
15:45	16:00	Unavailable					Dr.Bug
16:00	16:15	Unavailable					Dr.Bug
07:00	07:15	Booked	Morgan,Dexter	Cooties	41	1111111111	Dr.Doom
07:15	07:30	Overbooked	Jerry	Rash	9	2222222222	Dr.Doom
07:15	07:30	Overbooked	Tom	Headache	20	3333333333	Dr.Doom
07:30	07:45	Booked	Pickachu	Back Pain	27	4444444444	Dr.Doom
07:45	08:00	Booked	Woody	Cough	18	5555555555	Dr.Doom
08:00	08:15	Booked	Buzz	Runny Nose	19	6666666666	Dr.Doom
08:15	08:30	Unavailable					Dr.Doom
08:30	08:45	Unavailable					Dr.Doom
08:45	09:00	Unavailable					Dr.Doom

 

 

                                                                               Schedule List                     Page: 1

                                                  By Doctor                       Date: Today

                                                Date 04/23/2019                                                        User: Mitchell

 

Dr.Bug                                

     Total Appts:   11 

 

 

 11:30    -  13:15   Available                                 

 

 13:15    -  13:30   Unavailable                                    

 

 13:30    -  13:45   Unavailable                                    

 

 13:45    -  14:00   Unavailable                                    

 

 14:00    -  14:15   Unavailable                                    

 

 14:15    -  14:30   Unavailable                                    

 

 14:30    -  15:15   Unavailable                                    

 

 15:15    -  15:30   Avaliable                                   

 

 15:30    -  15:45   Unavailable                                    

 

 15:45    -  16:00   Unavailable                                    

 

 16:00    -  16:15   Unavailable                                    

                                                                               Schedule List                     Page: 2

                                                By Doctor                           Date: Today

                                                Date 04/23/2019                                        User: Mitchell

Dr. Doom                            

     Total Appts:   8 

                     

 07:00    -  07:15   Booked         1                              

 

 Morgan,Dexter                 Cooties                               41   01/31/2019 1111111111   ABC1111111111

                              

 07:15    -  07:30   Overbooked     1                               

 

 Jerry                                          Rash                               9    01/28/2019 2222222222   ABC2222222222

                              

 Tom                                             Headache                     20   11/05/2019 3333333333   ABC3333333333

                              

 07:30    -  07:45   Booked         1                               

 

 Pickachu                   Back Pain                27   07/30/2019 4444444444   ABC4444444444

                              

 07:45    -  08:00   Booked         1                                

 

 Woody                       Cough                           18   11/02/2019 5555555555   ABC5555555555

                              

 08:00    -  08:15   Booked         1                               

 

 Buzz                                            Runny Nose                       19   12/26/2019 6666666666   ABC6666666666

                              

 08:15    -  08:30   Unavailable                                    

 

 08:30    -  08:45   Unavailable                                    

 

 08:45    -  09:00   Unavailable                                    

------------------------------
Vaughn Mitchell
Data Analyst

------------------------------"