Aug 9 2017

Some useful tips for webpagetest.org

  1. Loading use http://www.webpagetest.org/?pngss=1&iq=80 for better image quality
  2. When comparing two or more videos you can change the run you want by adding -r:X to the text ID

    E.g.
    http://www.webpagetest.org/video/compare.php?tests=170809_2Q_016f5667941e2bda85149a234119e4c9%2C170809_R6_607a7af2bd5769e60d7d4c429f85e5f6&thumbSize=200&ival=100&end=visual

    becomes

    http://www.webpagetest.org/video/compare.php?tests=170809_2Q_016f5667941e2bda85149a234119e4c9-r:3%2C170809_R6_607a7af2bd5769e60d7d4c429f85e5f6-r:2&thumbSize=200&ival=100&end=visual

Aug 16 2016

UK date from a mixed format US date in Excel

Quite often if you open a CSV file in Excel with American date formats Excel will do its best to convert them (badly) and you end up with some in date format and some in text format.

This formula helps to sort it out.

DATEVALUE(IFERROR(MONTH(A2),SUBSTITUTE(MID(A2,FIND(“/”,A2)+1,2),”/”,””))&”/”&IFERROR(DAY(A2),SUBSTITUTE(LEFT(A2,2),”/”,””)&”/”&RIGHT(A2,2)))

So 6/21/16 becomes 21/06/2016  and 5/29/16 becomes 29/05/2016

May 5 2016

Query between two times in redshift

This is something I do regularly, wanting to look at log activity over a 30 minute range

Use datediff and “mins” datepart like this

(datediff(mins,’2016-05-04 15:50′,DATE) between 0 AND 30)

This looks at a 30 minute window around 16:00 hrs

Simple really

Apr 1 2016

In Redshift concatenating null strings

This should be easy

url_domain || url_path || url_query_params  as request

really should work.

BUT if any one of these is null it will deliver a null string

So you need

nvl(url_domain,”) || nvl(url_path,”) || nvl(url_query_params,”) as request

This replaces a Null with a blank.

Why can’t it do this anyway? Who knows.

 

 

Mar 11 2016

Nearest 5 or 10 minutes in redshift sql

This is quite straight forward but seems to cause confusion on the internet

Time to the nearest 10 minutes

TO_CHAR(DATE,’HH’) || ‘:’ || trim(TO_CHAR((ROUND((DATEPART (MINUTE,DATE) / 10),1)*10),’09’)) AS mins10

so you get

12:00  – 12:00
12:01 – 12:00
12:03 – 12:00
12:11  – 12:10

This imports into excel as a time

Time to the nearest 5 minutes

Just change the multiplier

TO_CHAR(DATE,’HH’) || ‘:’ || trim(TO_CHAR((ROUND((DATEPART (MINUTE,DATE) / 5),1)*5),’09’)) AS mins5

Time to the nearest minute

date_trunc(‘minute’, Date)

Time to the nearest hour

date_trunc(‘hour’, Date)

Day of the week

date_trunc(‘week’, Date)

This puts you to the nearest Monday in that week which is more useful than the week number

Time only

to_char(DATE,’HH24:MI’)

 

Aug 19 2015

How to open 2 copies of Excel at one time – very useful

It is quite easy and very useful if you are running complex and long calculations.

  1. Add Excel to you Start Menu. – In the program files right click Excel and chose “Add to Start Menu”
  2. Shift Click Excel on Start Menu – this opens a new version separate from any previous one.
  3. Add Excel to you Taskbar. – In the program files right click Excel and chose “Add to taskbar”
  4. Shift Click Excel on the Taskbar – this opens a new version separate from any previous one.

As far as I can tell you can only run two versions at a time.

Mar 4 2015

Make your excel lookups 100 times faster using Match

This method is almost instant (using 500,000 record lookups)

IF(M56=INDEX(‘Sitemap Keyword Categories’!$G$1:$G$300016,MATCH(M56,’Sitemap Keyword Categories’!$G$1:$G$300016,1),1),”Supported”,”Not Supported”)

This does an “nearest match” (using status “1”) and then tests to see if the nearest match is correct (ie “M56=”).

This Updated 500,000 records in under 1 second – previous methods (exact match using “0” and vlookup function ) would take 5 minutes.

It is vital that the column G in “Sitemap Keyword Categories” is sorted A-Z