Choose Language Hide Translation Bar

Navigating Your Data Workflow: Workflow Builder Grants Your Wishes for Data Cleanup (2022-US-45MP-1124)

What if you could save time in your process of collecting data, cleaning it, and readying it to begin your analysis? Accessing data and getting it prepared for review is often the most time-consuming part of creating a new data analysis or project. With that in mind, we would like to introduce the Workflow Builder in JMP 17. With this exciting new feature, JMP users can now record their entire process from beginning to end, starting with accessing data from multiple sources. Working with the action recorder (added in JMP 16 to track steps and provide scripts that can be saved and reused), Workflow Builder tracks all your changes in data prep and cleanup, data analysis, and reporting. In this presentation, we will show how to operate the Workflow Builder, save each action, and then replay and share them in a polished report. This is sure to become your new favorite feature in JMP 17.  No manual clean-up means extra time in your day!

 

 

Hi,  thanks  for  joining  us  today.

I'm  Mandy  Chambers and  I'm  a  principal  test  engineer

in  the  JMP  development  group.

And  I'm  going  to  talk  to  you  today about  a  brand  new  feature  for   JMP 17.

It's  the  Workflow  Builder.

And  I  want  to  talk  to  you about  how  to  navigate  the  data  workflow

and  just  sort  of  the  idea   behind  the  fact  that

the  Workflow  Builder  sort of grants  all  your  wishes  for  data  clean- up.

What  is  the  Workflow  Builder?

It's a  new  utility  that  records   your  data  preparation,  clean- up,

and  analysis  steps,  and  it  makes  it  easy   to  create  a  workflow

that  you  can  use  over  and  over  again.

During  the  EA  cycle,  we  did  a  survey   where  we  went  out

and  we asked  our  users  how  they  might  be  interested

in  using  the  Workflow  Builder.

And  we  got  a   smattering of  answers,

but  a  couple  of  the  top  suggestions   where  people  thought  they  might  use  it

for  sharing  work  with  others

who  might  be  needing  to  do   the  same  actions,

they  might  use  it  for  reusing   the  entire  sequence  of  steps

again  and  again,  or  taking  those  steps and  applying  them  to  new  data.

And  some  people  said  they  would  use  it for  archiving  their  work

and  documenting  maybe  past  work

so  they  wouldn't  forget how  they  did  things.

There  were  lots  of  answers  all  in  there, educational  purposes,  teaching  JSL.

Today  what  I'm  going  to  do

is  I'm  going  to  introduce  you to  the  Workflow  Builder

and  then  I'm  going  to  show  you several  samples  of  workflows

that  I  created  to  demonstrate some  of  these  actions.

Let's  get  started

and  let  me  share a  couple  of  example  workflows.

I'm  going  to  open  up  this  workflow

and  I'm  just  going  to  show  you what  it  looks  like.

This  is  one  I  created using   Big Class Families.

And  then  I  just  made  value  labels and  ran  a   Graph Builder.

And  so  what  we're  going  to  do instead  of  running  this  one

is  we're  going  to  create  one  from  scratch.

The  way  you  open  the  Workflow  Builder

is  you  go  to F ile,  New, and  you  go  down  here

and  you  select  New  Workflow

and  you'll  see  that  the  workflow  opens  up and  it's  completely  empty.

There's  nothing  in  it.

In  order  to  activate  the  workflow,

you  need  an  action  such  as   opening  a  table  or  importing  your  data.

We're  going  to  start  by  opening...

The  steps  will  go  in  here  as  you  open  things.

And  this  is  a  recording  log  history that  is  really  fed  from  the  enhanced  log.

The  script  that's  built when  you're  running   JMP is  fed  into  here.

If  I  leave  this  open,   which  I  will  today,

I'll  have  different  workflows  up.

The  history  of  what  I'm  doing  all  day

will  be  captured  in  this  lower part  of  the  Workflow  Builder.

Let's  go  over  and  open  up   Big Class Families.

And  I  want  you  to  notice that  as  I  open  this  up,

a  little  button  pops  up  in your  window

that  says,  "Hey,  do  you want  to  start  recording?"

This  is  an  option  so  you  can  check  this to  say,  "Don't  ask  me  this  again."

But  I  want  to  be  asked because  I want  to  know  what's  happening.

I'm  going  to  just  leave  it the  way  it  is  and  I'm  going  to  say  yes.

And  you'll  see  that here's  Big Class Families.

Notice  our  new  pet c olumn  out  here that  we've  added  for  JMP  17.

And  this  has  now  recorded this  step  over  here  to  where  it's  open

Big Class Families  and  it's  also put  it  down  here  in  the  history.

And  the  button  up  here  that  is  recording is  this  very  first  button.

And  I  don't  know  if  you  noticed at  first  it  was  solid  red

and  now  that  I'm  recording, it's  kind  of  hollowed  out.

It's   white in  the  middle.

Let's  do  a  couple  of  things that  we  would  normally  do

if  we  opened  up  a   JMP data  table.

I'm  going  to  go  into  this  column

and  recode  it  and  I'm  just  going  to  make titlecase  because  that  will  be

something  that's  simple and  I  don't  want  an  extra  column.

I'm  going  to  say  recode  this  column   in  place  so  we'll  be   saying name  column.

And  then  I'm  going  to  say  Recode.

And  you  can  see  right  here, that's  been  added.

And  then  I  want  to  go  to  this  step and  go  to  Column  Properties

and  I'm  going  to  add  my  value  labels  here.

And  so  if  I  can  type,  I'm  going  to add  this  and  add  some  labels.

And  you  can  see  the  labels  are  changed and  there's  the  comment  there.

And  then  I'm  just  going  to  grab a   Graph Builder  that's  out  here

and  run  it.

And  there's  a   Graph Builder.

Now  you'll  notice  that  when  I  ran the   Graph Builder,  it  did  not  get  recorded

in  the  workflow  builder  steps, nor  did  it  get  put  down  here.

Platforms  do  not  get  recorded unless  you  close  them

and  then  they're  recorded  as  actions.

Or  we  had  some  feedback  during  the  cycle

that  it  would  be  better to  be  able  to  save  those  if  you  wanted  to.

Under  the  platform's  red  triangle  menu,

you  can  go  here,  say  Save  Script, and  say T o  Workflow,

so  you  could  add  it  here.

I'm  not  going  to  do  it  this  way  right  now.

I'm  just  going  to  close   Graph Builder and  you  can  see  it's  been  added.

I'm  done  with  my  workflow that  I  want  to  demonstrate  here.

I'm  going  to  stop  recording.

And  then  the  second  button  is  a  button that  sort  of  resets  everything.

It  will  close  up  everything and  reset  the  workflow.

Now  I  can  rerun  it,  which  I  will  do.

Before  I  do  that, I  want  to  talk  about

a  couple  of  other  places   where  you  can  record  your  actions.

The  enhanced  log  or  the  log  file  here

is  another  place where  you  can  get  actions.

As  I  said,  all  of  this has  been  tracked  in  here.

We  opened  the  table, we  created  a  recode.

We  did  value  labels, we ran  our   Graph Builder.

Under  this  red  triangle  menu,

we  also  added  a  save  script  that  says you  can  save  script

and  add  it  to  the  workflow.

I  could  click  this  and  it  will  add  it to  the  workflow  and  notice,

it added  it  to  the  workflow and  I'm  not  in  record  mode.

You  can  go  and  grab  things.

Let's  say  you're  working throughout  the  day

and  you  do  several  things and  you  forget  something.

You  can  go  back  and  find  it  here

or  you  can  find  it  in  the  log down  here  and  you  can  push  it  up.

I  could  grab  this  recode  and  I  could grab  this  and  I  could  push  it  up  here.

Again,  I'm  not  recording, it's just  adding  things  to  my  workflow.

Now,  I  don't  need  all  multiple  steps in  here,  so  I'm  going  to  go  to  this  one

and  right  click   and  I'm  going  to  remove  it.

And  the  recode,  I  don't  need   a  second  recode,

so  I'm  also  going  to  remove  that.

I  have  four  steps  in  my  workflow

and  let's  just  click  the  third  button.

Here  is  a  button  that  will execute  all  the  steps.

These  other  buttons  are  buttons  that  do one  step  at  a  time,

or  you  can  go  backwards one  step  at  a  time.

But  I'm  just  going  to execute  the  workflow.

And  there  you  have  it.

It  ran  the  workflow.

You  can  see  my  column  here that  changed  the  names  to  title case.

And  the  labels  are  here for  males  and  females.

And so we  have  our  first  workflow.

We've  built  it. Yay,  that's  successful.

Let's  close  this and  let's  close  the  workflow.

And  I'm  not  going  to  save  it  right  now.

Let's  go  on  to  my  second  workflow.

I'm  a  big  fan  of  Virtual  Join, so  I  created  this  little  workflow  here.

And  what  I  did  was  I  went  and  opened  up

the  three  of  the  pizza  tables that  we  have  in  samples.

I  opened  Pizza  Profiles, Pizza  Subjects,  and  Pizza  Responses.

And  then  I  created  my  own  link  IDs

and  link  references  and I ran  a   Graph Builder.

And  so  I'm  going  to  just  run  this  workflow

and  then  we're  going  to  do a  couple  of  things  to  change  it.

Here's  the  workflow.

You  can  see  that  I've  opened  up  the  tables

if  you're  familiar  with  Virtual  Join, which  most  people  are  at  this  point.

Here's  where  I  created  a  link  ID.

Here's  another  one  with  link  ID.

And  then  here's  Pizza  Responses,

which  is  my  table  that's  actually driving  the   Graph Builder.

This  is  where  I  created my  link  references  for  these  columns.

And  this  is  the  table  that  I'm   using for  the  actual  platform  here  that  I  ran.

So  I  can  make  this  as  a  presentation and  I  can  make  it  a  little  cleaner.

I  don't  really  need  to  see  the  tables. It  would  just  be  nice  to  see  the  report.

I'm  going  to  show  you  a  couple of  things  more  about  the  Workflow  Builder

so  that  we  can  do  that.

If  you  go  to  this  panel  over  here, it  says  Step  Settings.

And  you  open  this  up,  this  is where  all  the  magic' s  happening.

This  is  where  all  the  JSL is  being  captured.

As  I  hover  over  this,  you've  probably seen  things  popping  up  like  this.

You  do  have  actions  where you...

There's tool  tips  under  here that  will  show  you  the  script.

But  there  are  a  couple of  things  under  here.

If  I  don't  want  to  see  this  table, we  have  a  couple  of  actions  built  in  here.

There's  a  thing  called  Show  Message which  I'll  talk  about.

You  can  create  subsets  and  random  seeds. You  can  do  custom J SL.

But  what  I  want  to  do  is I  want  to  hide  this  table.

All  of  a  sudden, here's  my  step  where  my  table  is  open.

And  then  here's  my  hide.

I'm  going  to  hide  all three  of  these  tables.

I'm  going  to  add  this  action

to  all  three  of  these  steps and  hide  the  tables.

And  then  there's  the  JSL  that  was  captured when  I  created  a  link  ID  and  another  ID.

And  here  are  my  references  and  so  forth.

And  there's  my   Graph Builder.

I'm  going  to  close  this  right  panel here  and  I'm  going  to  run  this  again.

And  this  time  you'll  see  it  run  through. And  there  you  have  it.

It  runs  through. We  didn't  see  any  tables.

It  hid  my  tables  for  me.

You  can  see  them  down  here in  my  JMP  Home  window  down  at  the  bottom.

They're  there  if  I  want to  open  something

and  I  want  to  run  another  report.

But  this  is  a  much  cleaner  report.

Let  me  point  out   a  couple  of  other  things

that  I  might  want  to  do  in  here.

I  might  want  to  slow  this  down a  little  bit.

Another  action  I  could  do  in  here is  I  could  add  a  custom  action.

And  so  let's  just  add a  wait  statement  in  here.

And  so  what  I'm  going  to  do is  I'm  going  to  type

just like  you  would normally  type  a  JSL  step.

Just write  there  to  say  wait.

I  kind  of  want  that  to  be  after  this  step.

I'm  going  to  push  it  down so  it  follows  the  step,

the  step  setting  the  link  reference.

And  there's  the  wait.

And  then  let's  run  this  again and  just  pay  attention  for  a  second.

See  if  you see it  hesitate before  it  runs t he   Graph Builder.

There's  the  hesitation and  there's  the   Graph Builder.

There  are  a  couple  other  menu  items.

I  think  it's  easier  to  show  you  these all  along  the  way.

If  you  want  to  save  your  workflow,

you  go  to  the  File  menu  and  you  can  say Save  or  Save  As  and  it  will  save

the  workflow  locally  for  you with a .jmpflow  is  the  ending  on  the  file.

If  you  want  to  add  this  to  a  journal, one  of  the  things  that's  been  put  together

for  us  is  the  ability  to  create a  journal  out  of  your  workflows.

You'll  see  here,  here's  the  open.

Here's  your  code so  you  could  run  your  code.

And  here's  the  report  at  the  bottom.

There's  a  thumbnail  here and  I  have  a  full- size  graph

if  I  want  to  see  it  there.

So  that's  really  a  nice  feature  for  this because  journals  are  sometimes  hard.

I  did  create  this  one  to  the  right,

but  I  have  create  them  a  lot  of  times and  save  them  and  reuse  them.

So  this  is  just  nice that  this  is  sort  of  built- in.

That's  a  nice  feature.

The  other  thing  that  you  can  do is you  can  go  up  here  and  you  can  say

save  your  script  to  the  script  window.

Just  so  you're  clear, this  creates  a  script

that  does  all  the  JSL that  we've  been  doing,

but  it  does  not  regenerate the  workflow  dialogue.

There  is  not  a  script that  will  create  that  window  for  you.

This  would  run  just  straight  script.

I  have  the  hide  function  in  here that's b een  created  to  hide  the  tables.

It  would  run  the  same  thing  and  just  run the   Graph Builder  at  the  end,

but  it  will  not  redo  the  workflow  window.

I  think  that's  mostly what  I  wanted  to  show  you  in  this.

One  other  thing  that  you'll  see  is there  is  the  ability  to  group

some  of  these  steps and  I  have  some  more  workflows.

I'll  show  you  where  I've  done  this.

But  the  way  that  you  would  do  this is you  would  right- click  and  say  Group.

You  might  want  to  do  that because  these  are  all  opens.

These  are  actually  steps where  I'm  changing  things

about  the  columns and  then  I'm  running  a  report.

You  have  the  ability to  do  groups  within  groups

and  group  some  of  your  workflow  together so  it's  a  little  cleaner

and  you  know  what  you're  doing.

Let's  close  this  workflow.

The  next  workflow  that  I  want  to  show  you

is  one  that  actually  Peter  Hersh  designed and  it's  more  along  the  line...

I  titled  it  Distribution  Education  Type.

And  he  had  done  this  cool  thing where  had  opened  up  a  data  table

and  then  I  believe  he  had  run  a  one  way and  then  he  had  gone  through

and  selectively  picked  different  areas of  the  platform  output

and  then   done  a  definition of  what  each  one  was.

I  kind  of  cloned  his  idea and  I picked  up  my  own  distribution.

And  so  I'm  just  running  a  distribution on  this  pain  column  here.

And  then  what  he's  actually  utilizing, and I'll  show  you  in  a  minute,

is  the  Show  Message  window where  he  selected  this

by  using  a  report  script.

And  then  he  basically  grabbed  a  definition for  quantiles and  freezing  for  a  minute.

It  won't  go  on  until  I  say  okay.

And  then  it  moves  to  this  next  section and  it  pulls  up  the  definition  here.

And  then  I'm  going  to  say  okay  again.

Notice  the  little  running  man over  here  as  well.

I  didn't  point  that  out  before.

It's  hesitated  right  now.

So  the  first  step  has  been  completed and  you  get  a  green  check.

But  the  little  running  man  is  here  now.

He's  kind  of  waiting  for  me  to  finish.

And  when  I  click  this, he  turns  into  a  green  check,  too.

Let's  close  this.

And  then  I  want  to  show  you another  feature  where  you  can  go  down  here

under  the  red  triangle and  you  can  duplicate  a  workflow.

And  so  I'm  going  to  go  into  this  workflow and  get  into  the  side  over  here

that's  kind  of  magical.

I'm  going  to  close  this  one because  I  don't  really  need  it  anymore.

And  then  I'm  going  to  show  you how  you  could  just  go  in  here.

Now,  you  can  recapture  this by  bringing  up  another  table

and  doing  all  the  steps  again.

Or  you  could  just  go  in  here and  you  could  do  a  little  typing.

And  I'm  not  the  best  typist, but  I'm  going  to  do  it  this  way.

I'm going  to  go  in  here

because  I  want  to  show  you how  this  was  done.

There's  a  distribution  running  here and  the  distribution  was  captured.

But  he  went  in  here  and  he  added  this and  assigned  it  to  a  report.

He's  just  calling  it  Report  OW.

I'm  going  to  go  in  here  now

and  I'm  going  to  change  the  table to  the  body  fat  table.

And  I'm  going  to  pick  a  different  column, percent  body  fat.

And  I  need  to  type  it  right.

And  then  this  is  the  part  down  here where  he's  selected  it,

and  he's  doing the  definition  of  quantiles.

So that's  the  first  part  of  this.

And  then  this  action  here is where  he  added  Show  Message.

The  Show  Message  step  is  right  here.

And  he  typed  in  quantiles.

We  typed  in  a  definition  here.

He  selected  it  to  be  a  modal  window.

And  then  he  went  to  the  next  step, which  is  a  custom  action,

the  clear  action.

And  he  did  that  by  selecting  custom  here and  he  named  it  clear  selection.

So  he's  taking  the  report and  saying d eselect it,

just  straight  up  JSL.

And  then  the  next  step is  to  select  the  next  part  of  this,

which  I  need  to  change  it   to  percent  body  fat.

And  he's  selecting  the  summary  statistic

and  then  the  Show  Message  for  that is  summary  statistic  and  the  definition.

So  if  I  typed  this  correctly, and  hopefully  I  did,

we  should  be  able  to  close  this  side, go  over  here,  and  run  this  again.

There's  the  body  fat  table, different  table  open.

Here's the  body  fat  distribution.

Quantiles  are  selected  here.

There's  my  quantiles  definition. Say  okay.

And  now  there's  the  summary  statistic and  the  definition.

This  is  kind of  a  cool  thing.

It's  a  neat  way  to  use  it for  maybe  a  teaching  tool,

some  kind  of  educational  piece.

But  I  just  thought I  would  show  that  quickly

because  it's  just another  way  to  use  workflow.

Let's get into...

I  decided  that  it  would  be a  little  bit   more  interesting

to  maybe  show  you  a  real- life  example.

qwas  talking  with  my  husband,  actually, and  we  were  talking  about

what  kind  of  data  I  could  go  and  find.

And  we  were  talking  about this  payroll  protection  program

and  sort  of  a  real  data  example.

So  this  is  government  data.

It's  all  public  knowledge.

I   was  able  to  get  in  there

and  really  drill  into  court  orders and  court  proceedings.

You  can  see  people's  names.

There's  a  way  to  search  in  every  state for  any  kind  of  company.

There's  a  lot  of  data  out  there.

I  took  a  smaller  table,

which  I'm  giving  you  guys an  abbreviated  version  of  the  journal.

I  didn't  give  you  the  whole  thing.

But  you  will  get  this  table. I  believe  it's  in  there.

This  is  a  smaller  list,

but  it  was  a  pre- alleged PPP  fraud  data  list.

And  so  they  went  through,

and  they've  actually  tried and  called  and  found  people.

Couple  of  data  points  here.

The  accused  folks  were  seeking about  $250 million  in  loans,

but  they  actually  obtained  about 113 million  which,  okay,  to  me,

that's  not  small  amounts  of  money.

Joshua  Bellamy  was an  ex-NFL  football  player

that  played  for  the  New  York  Jets, and  he  conspired  with  this  guy,

Phillip  Augustin,  with  Drip  Entertainment, which  is  sort  of  a  music  industry...

And  they   connected  together from  Florida  and  Ohio.

And  I  kind  of  have  a  map  where  I  can  show how  I  went  in  and  found  the  two  of  them.

And  I  think  they  came  up  with  about $17  or  24  million  or  something.

But  it  was  all  fraudulent  money.

This  place  called   Papillon Air,

I  believe  they  got  a  large  amount of  money,  too,  but  they  took

about $2.5 million  and  purchased luxury  cars  and  a  private  plane.

There  were  people  in  Houston, one  individual,

that  went  through  and  applied for  about  80  different  loan  applications,

working  with  various  different  people, with  fake  companies,

fake  different   licenses  and  agreements,

and  he  purchased  a  Lamborghini and  a  lake  property.

And  I've  got  a  little  note  here  that  says

you  need  to  be  really  careful with  your  text  messages.

I  read  some  of  the  correspondence in  that  particular  court  order,

and  it's  right  there  in  the  court  order

as he's  texting  these  different  people all  over  the  country  saying,

"Hey,  it's  time  that  we  go and  file  our  tax  form,"

for  this  and  that  and  everything  else,

and  it's  all  right  there, so  just  be  careful  with  what  you  do.

This  is  read in from  the  government.

It's  a  straight- up  Excel  file.

I  created  the  whole  thing,  reading  it  in and  capturing  stuff  and  cleaning  up.

Here's  an  example of  where  I imported  the  data.

You  can  see  groups  here.

I  grouped  columns to  change  names  of  columns, formats,

change  things  to  multiple  response

so  they  would  do  better in  a  mapping  situation.

I've  got  labels  in  here,

selected  deleting  rows and  hiding  and  excluding  certain  things,

selection  of  rows  to  create  markers and  colors  to  make  another  map.

And  then  there  are  several  reports that  we'll  run.

I  want  to  thank  Lisa  Grossman— I'll thank  her  at  the  end,  too—

but  she  helped  me with  some  of  these   Graph Builders.

She's  on  my  team and  I  appreciated  her  helping  me

so I  could  show  some  of  the  features that  are  in   JMP 17  for   Graph Builder.

And  you'll  notice  down  here there  are  several  reports.

And  this  particular  report  right  here

is lighter  and  it's  italicized  to  the  right.

And  I  just  want  to  say  that  the  way...

That's  a  report  that  I'm  not  showing  you, I  didn't  want  to  delete  it,

but  the  way  that  you  enable and  disable  that  is  you  right  click

and  you  can  say  step  enabled or  not  enabled

and  I'm  going  to  take  it  out.

I  didn't  really  want  to  delete  it because  I  might  want  to  use  it

some  other  time. So  this  is  a  nice  way  to  say,

"Hey,  I'm  running  some  stuff,

but  I  only  need  to  show  somebody maybe  one  or  two  things."

That's  just  a  way  to  keep  something  in  there

and  not  lose  what  you've  already  captured.

Let's  run  this  workflow.

And  you  can  see  it  runs  fairly  quickly.

Now,  it  took  me  a  while to  build  the  workflow.

Let's  go  through  and  let's  just  talk  about a  few  of  these  things.

This  is  a  Text  Explorer.

I'm  just  a  big  fan  of  Text  Explorer because  I  like  word  clouds.

But  this  is  the  DOJ  records.

They  had  a  column   where  they  were  talking  about

what  all  happened  in  that  particular  aspect  of  the  charge

against  these  different  people.

It's  just  interesting to  see  the  words  here.

According  to  allegations  and  allegedly and the  millions  and  the  companies.

You  can  see  PPP  funds and  that  kind  of  thing.

That  was  really  just  fun  for  me. I  wanted  to  show  you  that.

Here's  a   Graph Builder  that  we  designed.

And  I  made  myself  some  notes.

I   used  the  aspect  of  this  part  of  the  workflow

so  I  could  remember  what  I  wanted  to  say about  some  of  these  things.

There's  a  little  notes  section  right  here.

The  comment  we  made  here was  this  Hawaii  guy  really  cleaned  up.

I  don't  know,  he  tried to  get  about  $18  million.

He  got  almost  13.

I  guess  that  was  good  for  him at  some  point,  but  not  in  the  end.

And  it  said  he  ended  up  falsifying,

how  many  different  employees were  there,  and  all  of  that.

But  you  can  see  in  the  map  here, the  red  is  kind  of  honed  in  on him.

That's  where  he  was  located.

That  is  just  a  regular  map using  the  colors  here.

And  then  this  particular  map is  a  map  of  the  states with...

I  called  it  states  with  red  flags.

And  in  looking  at  this, this  is  where  I  went  into  the  data

and I actually  was  able  to  find— and  I'll  pull  the  table  over  here—

I  was  able  to  find these  two  guys  pretty  easily.

This  guy,  Phillip  Augustin,  is  right  here.

And  then  Joshua  Bellamy  was  down  here.

And  as  I  was  looking  at  these,

I  was  able  to  go  across and  see  what  they  actually  did.

Joshua  played  for  the  New  York  Jets and  lived  up  near  New  Jersey.

When  you  link  these  together, you  can  see  that  some  of  the  addresses

maybe  fall  in  Florida, but  other  places  fall  in  Ohio.

And  then  the  address  he  used  for  the  company

was  Cross  River  Bank, which  was  up  in  New  Jersey.

And  then  the  notes  that  are  written  here

are  that  this  guy  up  here  was  using this  Clear  Vision  Music  Company

and  they  got  $17  million  in  funds.

They  filed  about 90  different  fraudulent  applications

for  the  millions  of  dollars  that  they  got.

And  then  Joshua  was  down  here. So there's similar  comments,

but  the  company  here was  Utilization  Review  Pros,

and  the  company  here was  Clear Vision  Music  Company.

So  just  an  interesting...   The  map  that  Lisa  made  me  here

was  kind  of  cool  because  the  one  I  saw the  government  had  was  very  flat

and  had  little  red  flags  in  it.

We  kind  of  designed  it  this  way.

Then  this  is  just the  distribution  that  I  had

because  it's  an  easy  way to  see  the  states.

Then  you  can  see  Florida  here,  Georgia,

looks  like  New  York  and  Texas were hot  with  the  fraudulent  areas.

We  had  this  other  field  out  here where  it  said,  "Did  they  plead  guilty?"

We  were  looking  at  that,

wondering  how  many  different places  did  they  plead  guilty.

Did  it  make  any  difference?

It  didn't  seem  to  make  any  difference.

And  I  have  another  graph in  the  next  set  of  data.

I  just  wondered  if  maybe they  got  off  easier.

I  don't  really  know.

But  if  you  want  to  look  at  that  later,

I  do  have  the  links  in  there for  all  of  this  to  be  able  to  show  you.

So  that's  that  particular  workflow.

I  wanted  to  go  into  the  bigger  workflow. Let  me  go  down  into  this  one.

This  is  the  single  entries for  every  one  of  the  PPP  loans.

And  I  think  they're  about $1.6  million  in  rows

or  1.6  million  unique  entries.

I  went  in  and  I  created a  smaller  workflow

that  actually  goes  through and  imports  the  data,

concatenates  the  tables  together, and  saves  it.

And  then  I  went  to  a  second  workflow where  I   did  all  my  cleanup.

You  could  have  done  them  all  together, but  I   wanted  to  split  it  up.

This  is  an  example,  too, of  just  using  something

where  I  had  created  this.

And  I  realized  that  I  already  had the  table  out  on  my  desktop

so  I  didn't  want  to  be  redoing the  same  things  over  and  over

so  I  added  a  JSL  step,

just  a  custom  action  myself  to  say, "Hey,  go  look  for  this.

If  it's  there,  then  delete  it."

I'm  going  to  run  this  workflow and  pay  attention

to the little  running  guy  right  here.

He's  working,  he's  running  out to  go  get  the  data.

It's  kind  of  big,  he's  importing  it.

And  then  the  little  check  marks  are  done

as we're  running  through  this and  we're  concatenating  the  tables,

we're  getting  rid  of  those  tables, and  then  we're  saving  the  one  big  table.

All  I  did  was  open  that, run  it,  and  create  my  data  table.

I'm  going  to  close  this  up and  I'm  going  to  go  to  the  next  workflow.

And  now  I  have  that  table

and  I'm  ready  to  run my  bigger  workflow  here.

Again,  I  created  formats, I  standardized  attributes  here.

I  went  through  and  I  did  some  recoding.

I  wanted  to  get  some  latitude and  longitude  for  different  cities.

There  was  a  table that  got  opened  with  those

that got  updated  into  this  table

and  I  closed  that  table because  I  didn't  need  it.

I've  got  a  tabulate  report and  a  couple  more   Graph Builders

and  so  forth  and  distribution.

Let's  just  run  this  through.

Obviously,  it  takes  a  little  while to  build  these  things,

but  then  once  you've  built  it, you've  got  your  reports

and  you're  ready  to  roll so  you  can  see  how  quickly  it  runs.

This  is  a  distribution  that  I  ran and in  this  particular  data  table

because  it's  a  single  entry for  everything,  I  went  in

and  I  was  reading  and  to  figure  out, okay,  what's  fraud  and  what's  not.

You'll  notice  in  this  loan  status  here,

this  Exemption  4s  were  the  ones  that  were  fraudulent

and  so  those  are  the  ones that  are  interesting.

If  I  click  on  that  you  can  kind  of  see

that  almost  all  the  states had  maybe  something  in  there.

Not  quite  all  of  them,  but  some  of  them.

And  just  to  be  fair,

there  was  a  fairly  large  amount  of  money here  that  was  paid  back  in  full.

People  did  pay  back  some  of  the  loans.

I  was  trying  to  see if  things  made  differences

as  to  whether  they  were  corporations,

limited  liabilities,  sole  proprietorships, or  anything  like  that.

And  I  couldn't  really  see that  it  fell  one  way  or  the  other,

whether  it  was  fraud  or  not  fraud.

This  particular  graph  here, and  I'm  going  to  go  open  up

my  little  side  panel  that  helps  me  cheat  a  little  bit.

This  is  a   Graph Builder  that's  showing a  new  feature  in  JMP  17

that  I  want  to  point  out here  at  the  bottom.

And  it's  the  tabular  summations.

This  is  just  a  bar  chart  that  shows

the  total  amounts  for  each  loan  status and  it's  overlaid  with  the  business  type.

Again,  just   curious, did  it  make  any  difference

if  it  was  a  new,  existing  business, if  it  was  younger,  older, or  start  up?

And  I'm  not  sure  that  really  mattered.

But  the  cool  feature  down  here is  just  to  kind  of  see.

These  are  the  summations you can  now  get  in   Graph Builder.

I  can  show  you  how  to  do  that.

If  you  open  up the  control  panel  under  here,

there's  something  called  a caption  box,

and  the  location  here  is  an  access  table.

And  so  if  I  drill  down  on  this,

there's  an  access  table  here and  that's  been  selected.

That's  what  allows  you to  be  able  to  do  that.

It's  showing  the  sum  of  each  of  those different  business  age  descriptions.

And  then  my  graph  behind  here— I'm  going  to  show  you  in  a  minute—

uses  the   Axis Reference  Line, which  is  another  part  of  that.

That's  where  that  comes  from.

Let's  look  at  that  one.

This  was  a  graph that  was  actually  done

with  the   Axis Reference  Lines.

And  this  is  actually  showing the  average  current  approval  amount

per  business  type, whether  it  was  rural  or  urban.

And  the  comment  here  that  Lisa  made  was, "I  don't  know  what  it  is  to  be  an  employee

with  stock  options,  but  they  really cleaned  up  or  racked  up  here,"

if  you  look  at  that  bar  chart.

Again,  I  was  looking  at  these, trying  to  figure  out

does  it  have  anything  to  do with  whether  it's  limited  liability.

There's  that  partnership  here, there's  two  of  those

because  I  think  you  can  be  an  individual and  then  you  can  also  have  a  partnership.

I  didn't  see  any big  differences  with  that.

But  again,  here's  this access  reference  line

that's  showing  the  main  rural and  the  mean  urban.

That  is  just  a  marker that  you  can  now  add  in  there

so  that  you  have  a  little  bit  of  measurement

when  you're  doing  those  graphs.

This  particular  graph   is  using  the  latitude  and  the  longitude

which  we  brought  in. And  this  is  kind  of  showing  Hawaii.

And so in  this  particular  graph,

it  again  is  showing sort  of  a  new  feature  for  the...

If  you  go  in  here  and  look  at  the  graphs and  look  at  the  background  maps,

it's  doing  the  street  maps

and  there's  been  some  things  added underneath  the  selections  for  URLs.

And  this  is  using  the  map  box  satellite.

So that's  a  nice  look,  a  nice  graph.

Again,  this  is  Hawaii.

And  I  think  our  guy  from  my  first  map was actually  in  this  part  of  the  islands.

The  reddest dot  up  here is  this  one  up  here.

I'm  not  real  sure  about  that, but  I  do  remember  Hawaii  was  all  red

so it  seemed  like  there  was  a  lot that  went  on  in  Hawaii.

This  particular  tabulate I  wanted  to  show  you  here,

this  is  actually  showing a  feature  in  JMP  17

that  was  introduced  earlier  in  the  17  cycle.

You  may  have  seen  it  if  you  use the  EAs,  but  we  created  this  ability

to  do  pack  analysis  columns on  the  right- hand  side.

And  so  the  way  that  you  do  those is  you  can  go  over  here

and  I've  used  the  current  approval  amount with  the  forgiveness  amount.

They'r e  packed  together by  right- clicking  and  saying

pack  the  columns so  they're  packed,

can  unpack  them.

And  then  there's  a  template.

I  had  gone  out  and  the  templates in  here  with  the  first  and  the  other,

and  you  can  do  a  name  selector of  using  a  comma  or  something  else.

But  I  went  in  here  and  added a  little  spacing  in  here

and  I  changed  it  to  brackets.

I  believe  the  default  comes  up with  no  spaces  and  it's  parentheses.

It  kind  of  makes  a  nice  report.

Again,  I  was  looking  at  the  exemption  part  of  this,

which  is  the  fraud.

Here's  the  paid  in  full, which  is  a  little  bit  more  money  maybe.

But  if  you  look  at  the  exemption  part, I  kind  of  honed  in  here.

And  this  is  where  I  was  looking  at... Here's  corporation

that  said  30%  of  the  total and  the  limited  liability,

the  LLCs  are  about  29%.

Just  some  interesting  data  points.

Like  I  said,  I  give  you  the  references to  these  and  if  you  want  to  go  and  dig  in

and  look  yourself,  you  feel  free.

That  wraps  up the  Workflow  Builder  demonstration.

I  want  to  close  and  I  just  want  to  say thanks  to  the  development  staff

that  worked  really  hard  on  designing the  Workflow  Builder,  Ernest  Vasseur,

Dave  White,  Evan  McCorkel, just  to  name  a  few.

There  were  a  lot  of  people that  worked  on  this.

Julian  Paris  was  also  really  key in  the  design  phase  and  prototyping

and   helping  a  little  bit with  initial  testing.

Again,  I   thank Lisa for  the   Graph Builder  assistance  as  well.

There  are  references  here,  like  I  said,

included  for  the  PPP  data, so  you  can  look  at  that.

And  I  just  want  to  close  with  saying

that  I  think  Workflow  Builder  will  be the  best  new  feature  probably  in   JMP 17.

I'm  probably  a  little  biased,

but  I  think  it's  going  to  save  you  time with  your  data  clean  up  and  prep.

I  think  you're  going  to  get  more

out of  reusing  recorded and  repetitive  steps

that  you  find  yourself doing  maybe  every  day.

It  should  simplify  your  work  efforts and  maybe  accelerate  your  daily  processes,

but  it's  going  to  leave  you  a  lot  more  time  in  your  day  for  other  stuff.

So  try  it  out  and  we'd  look  forward to  talking  with  you  about  it

and  good  luck  and  thank  you for  letting  me  share  with  you  today.