Solving Logic Problems Using Errors

Quick reference

• 00:05 >> In this video we are going to look at using errors to be able to solve logic
• 00:09 problems where there doesn't appear to be a ready to exploit pattern.
• 00:14 So we're going to start by going in, creating a new queary,
• 00:18 to pull in some data from a text file.
• 00:21 Once again, we will browse down through into our files and
• 00:24 we'll go into GoSkills data in the conditional logic.
• 00:27 And the one that we want is called using errors and we'll say import.
• 00:32 This will take us to our nice preview window and we'll click edit to go in and
• 00:35 take a deeper look at the data itself.
• 00:40 Now, the first thing we know is that we can get rid of these
• 00:43 top four rows because we've already got all of the individual line items here.
• 00:47 So, let's get rid of those.
• 00:48 We'll say remove rows, remove top rows.
• 00:51 And we'll remove the top four rows.
• 00:54 We'll say, ok.
• 00:56 We'll then promote the next row to headers.
• 01:00 And we'll filter out from the first column the number of records rows
• 01:04 to get rid of those, as we don't need them.
• 01:07 And now, we run against a problem.
• 01:10 The challenge that we have is that we've got John Thompson and Bob Johnson.
• 01:14 And their names need to be associated with each record
• 01:17 all the way down these columns.
• 01:19 The challenge we have though,
• 01:20 is that there's no obvious or easy entry point in order to be able to do this.
• 01:24 We can't add a conditional column looking for Johnson or Thompson.
• 01:29 Because what happens when we get another employee?
• 01:32 We can't look for John or
• 01:33 Bob because if Mary suddenly joins the company, that's not gonna help us either.
• 01:38 Now we could potentially tag off of these numbers here and say if the value's
• 01:43 greater than say, 10, then maybe we could go and deal with something or
• 01:48 maybe 24 cuz nobody would ever work more than 24 hours in a day, that's impossible.
• 01:54 But the challenge with that is, that looks like it might be a total,
• 01:58 so what happens if we have a new employee that joins the company and
• 02:01 they only work two days that are eight hours each in that period?
• 02:05 That would actually trigger a false positive or not trigger at all,
• 02:08 so that's not gonna work either.
• 02:09 So we have an almost inaccessible problem in this case,
• 02:13 that there's nothing really to exploit for our logic.
• 02:18 So here's what we're gonna do.
• 02:19 We're gonna right click on the work date column and
• 02:21 we're gonna choose duplicate column.
• 02:24 This is gonna give us a new copy of the work date to work with.
• 02:29 Now, what we're gonna do, is we're gonna actually rename this column right now and
• 02:33 we're gonna call this one date.
• 02:36 I'm now going to change type and I'm gonna use locale for
• 02:40 this because these are obviously American English date formats.
• 02:45 So we'll change this to a date.
• 02:48 And we'll pick up English US and we'll say OK.
• 02:52 And naturally, when we do that, that's gonna trigger an error.
• 02:57 Because we can't convert Thompson into a date.
• 03:02 This is actually super useful because when we right-click on the date column,
• 03:06 you'll notice that we have the ability to replace errors.
• 03:10 So we can do that and we can say, let's replace the errors with null.
• 03:16 At this point, we now have a new column that has null values in it and
• 03:20 those can be exploited by actually using some conditional logic.
• 03:24 But the challenge that I have here is that I want to be a little bit more complex
• 03:27 than just saying, hey, if this is null, bring out Thompson.
• 03:32 I wanna get both of these guys.
• 03:33 And as a matter of fact, I'd like Thompson, John.
• 03:36 So, because I'm trying to pull two columns at the same time and add some extra text
• 03:41 in there, I'm not gonna be able to use the standard conditional column.
• 03:45 So I'm gonna go to add Column, Custom Column.
• 03:48 And from here, let's go and made a new column called Worker.
• 03:53 And what we're gonna start with, is we're going to say if the date column
• 03:59 equals null, we're gonna put in a harder turn and a space and type in then.
• 04:05 If that happens, then what I'm gonna do is I'm gonna say, work dates and
• 04:10 quote, comma, space, quote.
• 04:14 And we'll take the out column.
• 04:18 The spaces around the ands are not actually necessary but
• 04:22 it just makes it feel a little bit better.
• 04:24 If that's not the case, if work date equals null, then we'll grab these names.
• 04:28 But if it doesn't equal null, then in that case, we'll put in null value instead.
• 04:34 Now we'll say OK and what you'll see is that we get a nice little column that
• 04:38 has my First Name, Last Name in here, in the reverse order with commas in between.
• 04:44 I can now right click, Fill Down.
• 04:50 And then, to get rid of this data, I can filter out the date column and
• 04:54 say remove the nulls.
• 04:57 The workdate column is now no longer required because I'd already built that up
• 05:02 in the date where I triggered some errors, so we'll remove that.
• 05:05 We can convert the out column to times.
• 05:11 We can force our number of hours to decimal number.
• 05:14 And then, just because I wanna see them this way,
• 05:16 I'm going to move the Worker column to the beginning and the Date column second.
• 05:22 And there we go, we've got a beautiful set of data that I can now go and
• 05:27 rename Timesheet and load out to the worksheet.
• 05:33 So even though I didn't have a specific column I could use to actually extract my
• 05:38 conditional logic, I could duplicate one and change some types after triggering and
• 05:44 error, so that I can then go through and actually build the conditional logic.

Lesson notes are only available for subscribers.