Lesson 6 of 9 · Lab
Split packed cells into columns or rows
Split to columns turns one cell into several columns on the same row. Split to rows gives each part its own row and copies everything else, including the money, so a total after the split counts it again.
Both blocks split text on a character you choose, the splitter. Split to columns names the parts desk_1, desk_2 and so on; Split to rows repeats the row.
Marit, who owns fictional Kramerica Bikes, asks which extras sell and when in the day bikes go out, because that's when a shop runs short. The answers sit in extras and desk, two more columns you dropped in the first course: helmet;lock, and Tomas|09:30|T2 for who served, when and at which till. The stack holds September's 1,096 finished hires, and the bottom Summarize totals revenue after the split.
Split to rows turns helmet;lock into two rows. What happens to that hire's amount_eur?
It's copied onto both. One hire is now two rows, so a sum after the split counts it twice.
Unpack the cells and keep the money right
- Strata is Oplema's browser app for data prep, and this live flow opens on Split to columns. Its Splitter is a comma, which
deskdoesn't contain, so everything lands indesk_1. The Formula and Summarize beside it are red until you wire them. - Set Splitter to Pipe (|):
desk_1is the staff member,desk_2the time anddesk_3the till. - Drag from Split to columns to the Formula beside it, which takes the hour from
desk_2as text: its Output type is VARCHAR. Its Summarize shows bikes out by hour. - Select Split to rows: its Splitter is a comma too, so nothing changes yet. The bottom Summarize reads 1,096 rows and a
grossof 50337.5. - Set its Splitter to Semicolon (;). The bottom Summarize now reads 1,336 rows and 61487. The money went up by itself.
- Keep the extras count after the split, but total revenue where each hire is still one row. Check: 1,096 rows and 50337.5 again.
If you're stuck
Select the line into the bottom Summarize and press Delete, then drag from Union's right dot to it. Delete first: a block's input takes one wire, and dragging a second one onto it does nothing.
- Select the middle Summarize for the count of each extra.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).
What the two branches should show
Extras: helmet 479, none 372, child seat 246 and lock 239. Revenue: €50,337.50 with VAT, over 1,096 hires. The busiest hour is 11:00, with 255 bikes out.
Count after the split, total before it
240 hires have two extras, and their €11,149.50 is exactly what the split added. Count extras on the long table and total money on one row per hire, each from its own branch.
desk needed columns because each part means something different; extras needed rows because each part is the same kind of thing.
Your line for Marit: "Helmets go out on 479 hires. Revenue is unchanged at €50,337.50 gross. Most bikes go out at 11:00."
See the Split to columns and Split to rows references. For later: Join fan-out shows the same inflation coming from a join.
Common splitting mistakes
- Totaling after Split to rows. Every copied row carries the whole amount.
- Choosing the wrong splitter. A splitter that never occurs splits nothing, with no error. Check that
desk_2exists. - Splitting into columns when the count varies. Rows handle any number of extras.
Splitting in SQL and Excel
SELECT split_part(desk, '|', 2) AS time FROM hires;
SELECT unnest(string_split(extras, ';')) AS extra, count(*) AS hires
FROM hires GROUP BY extra;unnest repeats the row like Split to rows, so sum amount without it. In Excel, Text to Columns splits into columns, and =TEXTSPLIT(J2, ";") returns the parts.