February 11-13, 2019 Raleigh, NC

1. February 11-13, 2019 Raleigh, NC

2. Advanced Flow Steps Getting the Most Out of Your Data

3. Agenda What are Flow Steps? Basic Flow Steps Advanced Flow Steps PowerScript

4. What Are Flow Steps? Calculations performed as data streams in from query Manipulate existing data or derive new values Can have any number of steps Steps are performed sequentially

5. Examples of Basic Flows

6. Basic Flow Steps - Calculated Column Similar to Informer 4 Can’t reference Java objects Must return a value Regular JavaScript Use for basic calculations

7. Basic Flow Steps - Concatenate Joins multiple fields together Space=default delimiter

8. Basic Flow Steps – Coordinates from Lat/Long

9. Basic Flow Steps - Counter

10. Basic Flow Steps – Fields from another Dataset Need common key value Pull back first or all matching records Not recommended for large Datasets

11. Basic Flow Steps – Java Calculated Field • New in 5.0.18 • Same as Informer 4 • Can reference Java objects • Migration bucket • All migrated 4 calculations • Should be converted for performance

12. Basic Flow Steps – Percent of Total

13. Basic Flow Steps – Fields from another Datasource Links to another Datasource Must have common key field Can pull first or all matching records Specify which fields to retrieve Should not be used in lieu of direct linking

14. Basic Flow Steps – Templated Field Same as Informer 4 Can not perform calculations Does not change data Similar to mail merge

15. Basic Flow Steps – Time Between Calculates time between two dates Use fields and/or literal date values Time units: seconds, minutes, hours, days,

16. Basic Flow Steps – Zip2Geo Converts zip code to geolocation value Relative center of zip code Use for heat maps

17. Advanced Flow Steps - Transform

18. Transform Flows – Field Settings Change the column’s label (header), Data type and/or Default Value

19. Transform Flows – Find & Replace Finds values in the result set and substitutes the replacement value. Good for data cleansing where common misspellings or inconsistencies in data entry occur.

20. Transform Flows – Merge Duplicates Merges rows together, displaying the unique matched values once and the remaining fields as either an array or the first/last value of the array. Useful when dealing with data having a one-to-many relationship.

21. Transform Flows - Normalize Opposite of Merge Duplicates. Expands array values into separate rows and repeats any non-array values for each array entry.

22. Advanced Flow Steps - Remove

23. Remove Fields

24. Advanced Flow Steps - Advanced

25. Advanced Flows - Flush

26. Advanced Flows - PowerScript Use the NodeJS vm library Plain old JavaScript with a sandboxed script context. No more Java objects!

27. PowerScript vs. Calculated Column PS does not return a value PS can create multiple columns in single step PS can reference reserved variables and exposed libraries

28. PowerScript Context Variables/Objects

29. PowerScript $record • The current record being processed • Access existing columns via alias var name = $record.firstName + ‘ ‘ + $record.lastName; • Can replace existing values $record.firstName = “Tim”; • Create new columns $record.helloWorld = “Hello World”;

30. PowerScript $local • Creates local, internal variables $local.myLocalVariable = “123”; • Persists across rows • Important to initialize $local.xyz = $local.xyz || 0; • Does not appear in output

31. PowerScript $fields, $query, $datasource Access to various objects Typically would not need these See Help Center article at https://informer5.zendesk.com/hc/en-us/articles/115005113823 for more information

32. PowerScript $inputs Access inputs Key/Value pair, key=input Alias $inputs[‘myInputValue’] $inputs.myInputValue

33. PowerScript $index • Returns the current row number varrowNumber = $index;

34. PowerScript Functions and Libraries • $omit() • Removes the current record from the result set if ($record.ShipCountry === "France") { $omit(); }

35. PowerScript Functions and Libraries • Lodash • Library for manipulating arrays, numbers, strings, objects, etc. • lodash.com – be sure to use version 3.x documentation • Referenced by _ (underscore) $record.orderTotal = _.sum($record.orderAmount);

36. PowerScript Functions and Libraries • Moment • Library to parse, validate, manipulate, and display dates and times • momentjs.com • Can take a string, date object, moment object, number, or array as argument if (moment($record.ShippedDate).isAfter($record.RequiredDate)) { $record.shipStatus = "Shipped Late"; } else { $record.shipStatus = "Shipped On Time"; }

37. Useful PowerScript Examples Pivot arrays

38. JavaScript Object Notation (JSON) An object is a list of Key:Value pairs Value can be any data type Access Value with dot (.) notation or bracket [] notation

39. Pivot Arrays Using for loop for (vari = 0; i < $record.s_tudent_non_courses_admission_tests_assoc_stncNonCourse.length; i++) { var name = $record.s_tudent_non_courses_admission_tests_assoc_stncNonCourse[i].replace(/\./g, '_'); $record[name] = $record.s_tudent_non_courses_admission_tests_assoc_stncScore[i]; $field(name).type('integer'); } Using lodash _.forEach($record.s_tudent_non_courses_admission_tests_assoc_stncNonCourse, (test, ndx) => { $record[_.snakeCase(test)] = $record.s_tudent_non_courses_admission_tests_assoc_stncScore[ndx]; $field(_.snakeCase(test)).type('integer'); });