Pages

Thursday, 13 December 2012

Stuff I keep Googling for - Excel

Names of the days of the week from date 

=CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Age grouping 

=LOOKUP(A1,{0,30,40,50,60,70,80,90,100},{"0-29","30-39","40-49","50-59","60-69","70-79","80-89","90-99","100-109"})

Get Field from Delimited Text String 

This crazy looking solution was originally posted at EXCELFOX.
=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",999)),fieldnumber*999-998,999))

Calculate Odds Ratio from Standard Error and Beta

Odds Ratio =EXP(Beta)
P value =CHIDIST((Beta/Standard Error)^2,2)

Friday, 7 December 2012

Progeny - Degree of Relation extended

Progeny is a genetic pedigree and clinical data management software designed to provide user front end and database for any hereditary study. Simply, it is a Sybase back-end database with a shiny point and click front-end and with pedigree drawing functionality.

Users can create all sorts of fields in addition to system fields, like Name, Surname, DOB, etc. Most powerful of all types of fields is the computed fields, which accepts SQL queries.

Degree of Relation and Degree of Relation2 system fields are used to identify kinship, degree of relationship to the proband. Latter is good enough to identify 16 straightforward relatives, e.g.: Father, Mother, Sister, Brother, etc. Anyone else is listed as Unknown. For exampple system fields do not show: cousin, grandchild, and if the aunt/uncle are maternal or paternal. A workaround is the following code for a computed field. This is true for Progeny 8, hopefully this will be implemented for future versions as a system field.

CASE
WHEN {Degree of Relation2}=1 OR {Proband status}=1 THEN  'Proband'
WHEN {Degree of Relation2}=2 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Brother'
WHEN {Degree of Relation2}=2 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Sister'
WHEN {Degree of Relation2}=3 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Son'
WHEN {Degree of Relation2}=3 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Daughter'
WHEN {Degree of Relation2}=4 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Father'
WHEN {Degree of Relation2}=4 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Mother'
WHEN {Degree of Relation2}=5 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Paternal Grandfather'
WHEN {Degree of Relation2}=5 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Paternal Grandmother'
WHEN {Degree of Relation2}=6 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Paternal Uncle'
WHEN {Degree of Relation2}=6 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Paternal Aunt'
WHEN {Degree of Relation2}=7 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Maternal Grandfather'
WHEN {Degree of Relation2}=7 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Maternal Grandmother'
WHEN {Degree of Relation2}=8 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Maternal Uncle'
WHEN {Degree of Relation2}=8 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Maternal Aunt'
WHEN {Degree of Relation2}=13 AND {Gender}=1 AND {Gender Unknown}=0 AND {Degree of Relation}=0 THEN 'Nephew WifeSide'
WHEN {Degree of Relation2}=13 AND {Gender}=0 AND {Gender Unknown}=0 AND {Degree of Relation}=0 THEN 'Niece WifeSide'
WHEN {Degree of Relation2}=13 AND {Gender}=1 AND {Gender Unknown}=0 AND {Degree of Relation}=2 THEN 'Nephew SiblingSide'
WHEN {Degree of Relation2}=13 AND {Gender}=0 AND {Gender Unknown}=0 AND {Degree of Relation}=2 THEN 'Niece SiblingSide'
WHEN {Degree of Relation2}=14 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Husband'
WHEN {Degree of Relation2}=14 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Wife'
WHEN {Degree of Relation2}=15 AND {Gender}=1 AND {Gender Unknown}=0 THEN  'Brother-in-law'
WHEN {Degree of Relation2}=15 AND {Gender}=0 AND {Gender Unknown}=0 THEN  'Sister-in-law'
WHEN {Degree of Relation2}=16 THEN  'Twin of Proband'
WHEN ({Degree of Relation} = 3
     AND (     (SELECT degree2
                 FROM   st_data AS stdatasub
                 WHERE  stdatasub.personnumber = st_data.motherid
                        AND stdatasub.familyid = st_data.familyid) = 6
             OR (SELECT degree2
                 FROM   st_data AS stdatasub
                 WHERE  stdatasub.personnumber = st_data.fatherid
                        AND stdatasub.familyid = st_data.familyid) = 6
         )
) THEN 'Paternal Cousin'
WHEN ({Degree of Relation} = 3
     AND (     (SELECT degree2
                 FROM   st_data AS stdatasub
                 WHERE  stdatasub.personnumber = st_data.motherid
                        AND stdatasub.familyid = st_data.familyid) = 8
             OR (SELECT degree2
                 FROM   st_data AS stdatasub
                 WHERE  stdatasub.personnumber = st_data.fatherid
                        AND stdatasub.familyid = st_data.familyid) = 8
         )
) THEN 'Maternal Cousin'
WHEN ({Degree of Relation} = 2
      AND ((SELECT ISNULL(stdatasub.degree2, 0)
            FROM st_data AS stdatasub
            WHERE stdatasub.personnumber = st_data.motherid
            AND stdatasub.familyid = st_data.familyid) = 3
          OR
            (SELECT ISNULL(stdatasub.degree2, 0)
            FROM st_data AS stdatasub
            WHERE stdatasub.personnumber = st_data.fatherid
            AND stdatasub.familyid = st_data.familyid) = 3
          )
) THEN 'Grandchild'
ELSE 'Unknown'
END

Friday, 1 October 2010

MS Excel flag challenge

I came across to this challenge on Chandoo’s website.

“The flag project is very simple. Just take your country flag and make it using only Excel Charts.”  - Chandoo.

So, here(jpeg) and here(xlsx) is my version of Uzbekistan flag made using MS Excel 2007. The colours and size are from the flag that is displayed at Wikipedia. I used PS CS3 to get RGB values. The flag is made up from four overlapping charts:
1. Blue, white, green stripes - bar chart
2. Red stripes – bar chart
3. Moon crescent – scatter chart
4. 12 stars – scatter chart
More information about Uzbekistan flag can be found here.

Saturday, 20 February 2010

NHS Number validator for Oracle

Everyone registered with the NHS in England and Wales has their own NHS Number. Following function validates the NHS Number. It will return 1 if it is valid NHS number. The rules for validation is based on NHS DATA MODEL AND DICTIONARY.

CREATE OR replace FUNCTION Is_nhsnumber(newnhsno VARCHAR2)
RETURN NUMBER
IS
  check_digit  NUMBER;
  my_nhsnumber VARCHAR2(20) := Trim(newnhsno);
BEGIN
  IF newnhsno IS NULL --Return 9 if it is NULL
  THEN
    RETURN 9;
  ELSIF --All Digits/exactly 10 digits
  Translate(my_nhsnumber, '0123456789', '9999999999') = '9999999999' THEN
    check_digit := 11 - Mod(To_number(Substr(my_nhsnumber, 1, 1)) * 10 +
                            To_number(Substr(my_nhsnumber
    ,
                   2
                   , 1
                   )
                   ) * 9 + To_number(Substr(my_nhsnumber, 3, 1)) * 8 +
                   To_number(Substr(my_nhsnumber, 4, 1)) * 7 +
                   To_number(Substr(my_nhsnumber, 5, 1)) * 6 +
                   To_number(Substr(my_nhsnumber, 6, 1)) * 5 +
                   To_number(Substr(my_nhsnumber, 7, 1)) * 4 +
                   To_number(Substr(my_nhsnumber, 8, 1)) * 3 +
                   To_number(Substr(my_nhsnumber, 9, 1)) * 2, 11);
  ELSE
    RETURN 0;
  END IF;
  IF 1 = --check digit
     CASE
       WHEN check_digit = 11 THEN CASE
                                    WHEN 0 = To_number(Substr(my_nhsnumber, 10,
                                                       1)
                                             )
                                       THEN 1
                                    ELSE 0
                                  END
       ELSE CASE
              WHEN check_digit = To_number(Substr(my_nhsnumber, 10, 1)) THEN 1
              ELSE 0
            END
     END THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END is_nhsnumber;
/

Valid postcode generator for Oracle

Lately I have been looking at validation rules of the UK postcodes and came up with a function for Oracle that validates any UK postcode. While at it I decided to create another function that would return valid postcode. The only use for this function could be to generate dummy data.

There are six types of valid postcodes:

TYPE_1 AN NAA M1 1AA
TYPE_2 ANN NAA M60 1NW
TYPE_3 AAN NAA CR2 6XH
TYPE_4 AANN NAA DN55 1PT
TYPE_5 ANA NAA W1A 1HQ
TYPE_6 AANA NAA EC1A 1BB


Following function will return in random order all types of postcodes. It doesn't mean they exist, but they pass the validation rules.

CREATE OR REPLACE FUNCTION Rand_postcode
RETURN NVARCHAR2
AS
postcode NVARCHAR2(8);
x NUMBER;
i1 NVARCHAR2(23) := 'ABCDEFGHIJKLMNOPRSTUWYZ';
i2 NVARCHAR2(23) := 'ABCDEFGHKLMNOPQRSTUVWXY';
i3 NVARCHAR2(14) := 'ABCDEFGHJKSTUW';
i4 NVARCHAR2(12) := 'ABEHMNPRVWXY';
n1 NVARCHAR2(10) := '0123456789';
o1 NVARCHAR2(20) := 'ABDEFGHJLNPQRSTUWXYZ';
BEGIN
x := Round(dbms_random.Value(1,6),0);
postcode := Substr(i1,Round(dbms_random.Value(1,23)),1);
CASE x
WHEN 1
THEN postcode := postcode
||Substr(n1,Round(dbms_random.Value(1,10)),1);
WHEN 2
THEN postcode := postcode
||Substr(n1,Round(dbms_random.Value(1,10)),1)
||Substr(n1,Round(dbms_random.Value(1,10)),1);
WHEN 3
THEN postcode := postcode
||Substr(i2,Round(dbms_random.Value(1,23)),1)
||Substr(n1,Round(dbms_random.Value(1,10)),1);
WHEN 4
THEN postcode := postcode
||Substr(i2,Round(dbms_random.Value(1,23)),1)
||Substr(n1,Round(dbms_random.Value(1,10)),1)
||Substr(n1,Round(dbms_random.Value(1,10)),1);
WHEN 5
THEN postcode := postcode
||Substr(n1,Round(dbms_random.Value(1,10)),1)
||Substr(i3,Round(dbms_random.Value(1,14)),1);
ELSE postcode := postcode
||Substr(i2,Round(dbms_random.Value(1,23)),1)
||Substr(n1,Round(dbms_random.Value(1,10)),1)
||Substr(i4,Round(dbms_random.Value(1,12)),1);
END CASE;
postcode := postcode
||' '
||Substr(n1,Round(dbms_random.Value(1,10)),1)
||Substr(o1,Round(dbms_random.Value(1,20)),1)
||Substr(o1,Round(dbms_random.Value(1,20)),1);
RETURN postcode;
END rand_postcode;