At least for Bank of the Philippine Islands, they required me a copy of any Billing Statement that is currently being delivered to me in the new address.
It’s quite logical. BPI wants to ensure the new delivery address can receive the SOAs they’ll be issuing me.
After hitting the profit objectives in stock market trading, it is now time to put this into reality, i.e., translate the figures seen at COL Financial website to actual money at my bank account.
COL Financial made is quite easy:
Since this is my first time, I even called the Customer Service to confirm receipt of email but in the normal days you just have to send the request before 11AM and you’ll be able to pick up the check in the afternoon.
There is an option allowing COL Financial to make the deposit to your BPI, Metrobank or BDO account. However, I find my first time so special that I want a check as proof. 
Problem:
Given a Master List in “Sheet1” of an MS Excel workbook
| A | B | C | D | E | |
| 1 | Unit Price | Unit Price | Unit Price | ||
| 2 | UniqueID | Commodity | ABC Store | Sari-Sari Store | XYZ Store |
| 3 | 201201 | Apple | 50.00 | 49.50 | 50.50 |
| 4 | 201202 | Cherry | 37.50 | 38.00 | 38.50 |
| 5 | 201203 | Strawberry | 26.75 | 26.00 | 26.25 |
| 6 | 201204 | Mango | 18.00 | 18.25 | 17.75 |
| 7 | 201205 | Buko | 30.00 | 29.00 | 28.00 |
You are requested to fill up the Commodity and Unit Price columns in “Sheet2” of the same workbook.
| A | B | C | D | E | F | |
| 1 | ItemID | Quantity | Commodity | ABC Store Unit Price | Sari-Sari Store Unit Price | XYZ Store Unit Price |
| 2 | 201205 | 1 | ||||
| 3 | 201203 | 3 | ||||
| 4 | 201201 | 2 | ||||
| 5 | 201204 | 5 | ||||
| 6 | 201202 | 7 | ||||
| 7 | 201203 | 9 | ||||
| 8 | 201205 | 3 | ||||
| 9 | 201201 | 5 | ||||
| 10 | 201202 | 1 |
Solution:
For C2, here is the formula
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,2,FALSE)),0,VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,2,FALSE))
For D2, the formula is
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,3,FALSE)),0,VLOOKUP(A2,Sheet1!$A$3:Sheet1!$E$7,3,FALSE))
Explanation:
VLOOKUP function has the following parameters:
lookup_value: this should be the ItemID which needs to be matched to the UniqueID in the Master List.
table_array: the range in the Master List where the matching and picking will be made
col_index_num: the column index of the table_array where the value will be picked.
For C2, since we are looking for the Commodity, 2 means the second column of the table_array. For D2 since we are looking for the unit price of ItemID for ABC Store, 3 means the third column of the table_array.
range_lookup: use FALSE when you need an ‘exact match’ of lookup_value and the UniqueID in Master List.
When you are using Tomcat 6 as the web service then restarting it is as follows:
sudo service tomcat6 restart
Simple eh?
Problem:
Given a real number, write a JavaScript code to round it off to the “nearest five”. Examples:
Analysis:
The result would be an integer which is a multiple of 5.
Solution:
We will write the JavaScript code as part of an HTML file:
<html>
<head>
<script type=”text/javascript”>
// PART 2: the rounding off javascript code will be here
</script>
</head>
<body>
</body>
<!-- PART 1: this section will contain the HTML elements to visually display to user the transformation -->
</html>
Part 1:
We need three elements: a text box for the inputting of the number, a button to trigger the rounding off javascript code, and another text box to display the rounded off.
<input type="text" id="txtInput">
<input type="button" onClick="doRoundItOff();" value="Round It!">
<input type="text" id="txtOutput" >
Here the trigger is on the onClick event of the button which calls the doRoundItOff JavaScript function.
Part 2:
Here is the JavaScript code:
function doRoundItOff() {
var txtInput = document.getElementById("txtInput");
var txtInputValue = parseInt(txtInput.value,10);
var remainder = txtInputValue % 5;
var roundedValue = 0;
if (remainder == 2 ) {
var offsetDecimal = parseInt(txtInput.value*10,10) % 10;
if (offsetDecimal < 5)
{
roundedValue = txtInputValue - remainder;
}
else
{
roundedValue = txtInputValue - remainder + 5;
}
}
else if (remainder > 2)
{
roundedValue = txtInputValue - remainder + 5;
}
else
{
roundedValue = txtInputValue - remainder;
}
var txtOutput = document.getElementById("txtOutput");
txtOutput.value = roundedValue;
}
Explain! Explain! Explain!
We started the doRoundItOff function with identifying the txtInput HTML element. It is noteworthy to say that I usually make use as var in the JavaScript the id in the HTML element.
Next we take into account the remainder when the integer part of the input is divided by five. This remainder will determine which multiple of five we have to choose.
If the remainder is two then we take into consideration the decimal value of the input. If it is less than five then the input is closer to the largest multiple of five less than the input. Hence, we round down. Otherwise the input would be closer to the smallest multiple of five greater than the input and we round up.
If remainder is greater than two, the input is closer to the smallest multiple of five that is greater than the input. Thus, we round up. Otherwise, the input is closer to the largest multiple of five less than the input and we round down.
A while back I posted the inquiry http://nullpointer.ph/questions/3204/any-fix-to-sql-server-management-studio-timing-out-when-adding-a-new-column-to-a-table
Thanks to Alistair sharing his knowledge. After some research got the following fix
that is, navigating Tools > Options then looking at Designers > Table and Database Designers node you will have Table Options. Override the default value of Transaction time-out after: [____] seconds with your desired length of time.
Assuming you have an XML file structured as follows
<questionnaire>
<question>
<id>1</id>
<text>Which of the following is TRUE?</text>
<option1>6 is a perfect number.</option1>
<option2>All prime numbers are odd.</option2>
<option3>None of the Above</option3>
<answer>B</answer>
<explanation>B is FALSE because 2 is prime and even.
Perfect Numbers are such that the sum of their prime factors and 1 equals the number. Since the prime factors of 6 are 2 and 3 and 2+3+1 = 6, then 6 must be perfect.</explanation>
</question>
…
</questionnaire>
and there are a thousand more <question> … </question> set in it but you are interested only in retrieving the id and answer. Using SQL Server 2008, what SQL statements should you use?
Here it is:
-------------------------------------------------
DECLARE @FilePath AS VARCHAR(50)
DECLARE @Query AS NVARCHAR(MAX)
DECLARE @QuestionData XML
-- set the path to xml file
SET @FilePath = 'C:\Users\test\questions.xml'
SET @Query = N'SELECT @data = CAST(BulkColumn AS XML) FROM OPENROWSET(BULK N''' + @FilePath + ''', SINGLE_BLOB) AS XmlData'
EXEC sp_executesql @Query, N'@data AS XML OUTPUT', @QuestionData OUTPUT
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @QuestionData
SELECT
*
FROM
OPENXML (@hdoc, '/questionnaire/question', 10)
WITH (
id INT,
answer CHAR(1)
)
EXEC sp_xml_removedocument @hdoc
-------------------------------------------------
Simple eh?
