Multiple ternary expressions

This is how to use Talend to concatenate multiple columns (fields) into one field neatly.

Say we have three fields, row1.field1, row1.field2, row1.field3 that we want to insert into a output.notes column.

In the tMap component, use the following expression:

(StringHandling.LEN(row1.field1) > 0 ? "Field 1: "+row1.field1+" " : "")+(StringHandling.LEN(row1.field2) > 0 ? "Field 2 "+row1.field2+" " : "" )+(StringHandling.LEN(row1.field3) > 0 ? "Field 3 Address: "+row1.Address+", "+row1.Suburb+", "+row1.State+", "+row1.Pcode+" " : "")

What this does is check to see if field1 has anything in it (i.e. has a length longer than 0), if it does, it adds the field and a space after it to buffer it against the next field. If it doesn’t, then it doesn’t insert anything. It then does the same for fields 2 and 3.

Updated 2014-01-30 12h49m to put the code in a html code block and fix the double quotes which had been ‘smartly’ but wrongly changed.

Using a ternary operator in Talend Open Studio

I’m currently preparing some data for import into SugarCRM for a client. The source data has a column that contains the given names (i.e. “Jane Mary”) and a second column containing the surname (i.e. “Doe”).

Using Talend Open Studio we want to separate the given names into first_name and middle_names columns. So, how do we split the first name and middle name?

By using in the tMap component an expression.

For the first name, we do this:


StringHandling.INDEX(row1.GivenNames," ") > 0 ? StringHandling.LEFT(row1.GivenNames,StringHandling.INDEX(row1.GivenNames," ")) : row1.GivenNames

And to extract the middle name/s we use this expression:


StringHandling.INDEX(row1.GivenNames," ") > 0 ? StringHandling.RIGHT(row1.GivenNames,StringHandling.LEN(row1.GivenNames)-StringHandling.INDEX(row1.GivenNames," ")–1) : ""

This type of expression are known as a ternary operationsee examples.

The basic format of a ternary operation is this:

test condition ? do this if true : do this if false

Insufficient Disk Space Warning When Attempting to install

Quick easy fix for Insufficient Disk Space Warning When Attempting to Unpack and Install a Sage ACT! Update or Hot Fix. The error arises from the fact that the free space on the drive is a multiple of 4GB. The underlying issue is to do with a bug in InstallShield. To fix this create a file of 1GB, changing your free space available, to do so quickly, from a windows command prompt, on the drive affected run this command:

fsutil file createnew 1gbfile.bin 1024000

This creates a 1GB file. You no longer have free space that is a multiple of 4GB. Do the install, then delete the 1gbfile.bin file. More on fsutil on Technet.

Convert multiple Microsoft Excel files to .csv files with 3 clicks

I often get given Microsoft Excel files to import them into a database. Normally we’ll work with the files in the Excel to clean up data (phone numbers, Addresses, Titles, etc) but then save them as .csv files to do the actual import.

On my MacBook this is now an easy task because I’ve setup a ‘Service’ with Automator. In Finder I simply select the files I want converted, right click, select Services and select Convert Excel to csv files. Job done. Heaps quicker than doing them one at a time.

Screenshot of Convert Excel files to csv service in Automator
Screenshot of Convert Excel files to csv service in Automator
Screenshot showing service in use
Screenshot showing service in use

Get SAMBA running on linux

This article here describes how to get SAMBA running on a Raspberry Pi.

The protocol, which is known as the Common Internet File System (CIFS) – but was originally called Server Message Block (SMB), has been implemented on other operating systems including Linux. The most popular implementation is known as Samba and it allows devices like the Raspberry Pi to act as a CIFS file server. To put it another way, it allows a Windows PC to mount a folder on a Raspberry Pi and then copy, delete, read and write files on the it.
Installing and configuring Samba on a Raspberry Pi for basic file sharing is quite simple.

How to fix ssh “Remote host identification has changed” Error

Trying to connect to a host via ssh and I’m getting an error:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
[...].
Please contact your system administrator.
Add correct host key in /Users/benhamilton/.ssh/known_hosts to get rid of this message.
Offending RSA key in /Users/benhamilton/.ssh/known_hosts:2
RSA host key for [...] has changed and you have requested strict checking.
Host key verification failed.

The simplest way to fix this (that I’m aware of) is to edit the /Users/benhamilton/.ssh/known_hosts file and delete the second entry. Why the second entry, because the line in the error above tells us which line is the offending key at the end of the line.:

Offending RSA key in /Users/benhamilton/.ssh/known_hosts:2

I’m using Mac OSX, other flavours of *nix will have a slightly different path to the known_hosts file.

More info re this error can be found at Serverfault, MacWorld, and thegeekstuff.

Formatting of printed addresses

How should you format an address on printed labels and envelopes?

Formatting addresses for printing in Australia:

Typically, the address should be written in three lines:

  • The top line should contain the recipient’s name
  • The second to last line should contain the number and name of the street, PO Box or locked bag number (if applicable)
  • The last line should contain the place name or post office of delivery, state or territory abbreviation and postcode. This line should be printed in capitals without punctuation or underlining, with the postcode put last
  • For international mail, the country name should be in capitals on the bottom line

For the rest of the world, see Wikipedia’s entry on Address (geography).

Download a file on dropbox using wget on linux

Firstly, get the dropbox share link. It will look like this

https://www.dropbox.com/s/ad2arn440pu77si/test.txt

Then add a “?dl=1” to the end of that url and a “-O filename” so that you end up with something like this:

wget https://www.dropbox.com/s/ad2arn440pu77si/test.txt?dl=1 -O test.txt

Now you can easily get files onto your linux box that aren’t available elsewhere.

Sugar logic to calculate days an opportunity has been open

We want to find out how many days an Opportunity in SugarCRM has been open, using a calculated custom integer field in SugarCRM Studio. I’m assuming you’ve already created a new custom integer field, likely called something similar to “Days open”.

The sugar logic to calculate the difference between two dates is this:

abs(subtract(daysUntil($date1),daysUntil($date2)))

and the sugar logic to do an if then else is:

ifElse(test,if_true,if_false)

The first test we’ll do is if the sales stage is “Closed Lost” or “Closed Won”, if it’s true, we’ll calculate the days between the create date and the expected close date. If the sales stage is something other than “Closed Lost” or “Closed Won” then we’ll calculate the days between the create date and today.

This makes our pseudo sugar logic formula look like this:

ifElse(
	or(
		equal($sales_stage,“Closed Lost”),
		equal($sales_stage,”Closed Won”)
	),
	calc_days_create_to_expected_close_date,
	calc_days_create_to_today
)

Which, when we update it with the real sugar logic parts becomes this:

ifElse(
	or(
		equal($sales_stage,"Closed Lost"),
		equal($sales_stage,"Closed Won")
	),
	abs(
		subtract(
			daysUntil($date_entered),
			daysUntil($date_closed)
		)
	),
	abs(
		subtract(
			daysUntil($date_entered),
			daysUntil(
				today()
			)
		)
	)
)

Now, removing all the spaces we get this:

ifElse(or(equal($sales_stage,"Closed Lost"),equal($sales_stage,"Closed Won")),abs(subtract(daysUntil($date_entered),daysUntil($date_closed))),abs(subtract(daysUntil($date_entered),daysUntil(today()))))

This post was written in response to a question on LinkedIn.

Deleting posts from Ghost.io

After creating a new ‘blog’ over at benhamilton.ghost.io I imported all my blog posts from ben.hamilton.id.au – then realised that the post url structure was different. Unfortunately, there is no way to fix that just yet on the hosted platform (you can on a self hosted ghost setup).

Not wanting to have duplicate content in two places I wasn’t looking forward to deleting a lot of posts manually. Thus was born a short recipe in Keyboard Maestro (if on Windows you could use AutoHotKey to do the same).

In essence, it loops the required mouse clicks to delete a post.

Recipe to delete Ghost.io posts
Recipe to delete Ghost.io posts

Tweet about it!

  • How to delete multiple yoursubdomain.ghost.io blog posts  Buffer

SugarCRM how to calculate a persons age

In SugarCRM we oft record a persons birth date (in the field birthdate). Sometimes it’s useful to know how old this person is. So we create a new field age_c (note: this can be of field type Integer or Decimal) and then using this formula we can see how old they are:

floor(divide(subtract(daysUntil(today()),daysUntil($birthdate)),365.242))

small_4956707000If you use a Decimal field type you can remove the floor() portion of the formula so that it doesn’t round it down. This means you will see just how old they are to a decimal point. Personally I tend to prefer just knowing that they are 21 or 42 rather than 21.45 or 42.01.

photo credit: Fouquier ॐ via photopin cc

SugarCRM field visibility dependant on multiple values

I’ve often got cause to make fields in SugarCRM (Professional and Corporate editions) to only appear based on the value of another field. We can do this easily using SugarLogic in Studio using this ‘dependant’ formula:

equal($fieldname_c,"value_1")

However today I need to make this particular field visible if the field it is dependant on is one of a few values. Thusly a different ‘dependant formula is required:

isInList($fieldname_c,createList("value_1","value_2","value_3"))

Hat tip to Anton in Sugar Support for pointing to the ‘isInList()‘ instead of trying to build something with nested ifElse()’s.

Mac OS X Keyboard Shortcuts

As a recent convert to the Mac OS X platform (I bought a Mac Book Pro), I’m loving OmniOutliner for creating outlines and documents, with one small annoyance. The default keyboard shortcut isn’t easy enough for me to use to move items up and down the outline list.

So when I found this article Alternative Options and Shorter Shortcuts I saw that using the same method I’d be able to ‘re-map’ ⌘↑ and ⌘↓ to move items up and down the list. And it’s worked. ☺

Excel formula to convert minutes into Hours and Minutes

On occasion I end up with an Excel spreadsheet that has a list of items, each of which has a duration. It’s easy to sum up the minutes and get a total number of minutes (see B7 in the picture below).

But it would be nice to see that in “x hours y minutes.” don’t you think? Yup, so do I. And that’s what I’ve done in the C9 cell in the picture below.

Screen snippet showing a column of minutes summed up, and then convert that into human readable hours and minutes
Human Readable Hours and Minutes

So without further ado, here is the Excel formula to do this

=ROUNDDOWN(B7/60,0)&" hours "&MOD(B7,60)&" minutes."

Let me know if you find this helpful.