Tuesday, October 31, 2017

How do you use the STRING_AGG() function?

You can create comma separated, concatenated strings from values in columns in a database. The concatenated strings are separated by a separator which gets added except for the end of the string.

The full syntax is here:

STRING_AGG ( expression, separator ) [ ]

::=  
    WITHIN GROUP ( ORDER BY [ ASC | DESC ] ) 


If you have SQL Server 2016, this will not work as shown here.


But it does recognize it as a function because I am using SQL Server Management Studio v17


Why not download SQL Server 2017 RC2 and try?

What is Thimble used for?

It is from Mozilla.org. It is free and has many cool features. It's Remix mode allows you easily modify existing Thimble projects to suit your requirements.

Thimble is a one-stop program bundling code editor, web server, web browser and developer tools.

It has an unbelievable number of cool features. Sure, I will test drive it!

  • Make a change and see your changes in real time
  • Has handy code snippets
  • Has a built-in JavaScript console
  • Add your files (drag and drop) and manage them
  • Has both dark and light themes
  • Embedded tutorials-follow or create OK
  • Get code hints
  • Edit CSS right in the HTML file
  • Publish to Web
  • Pick colors in the editor
  • Test project on your mobile device
  • Has a DOM inspector to work with HTML elements
  • Has Image filters
  • Take selfies

You start here.

Here is a video that explains it all:

What is ODBMS? and Who is the leader in this segment?

This is the definition of Operational Database Management System(ODBMS) according to Gartner (5/10/2016):

"The operational database management system (OPDBMS) market is concerned with relational and nonrelational DBMS (see Note 1) products suitable for a broad range of enterprise-level transactional applications, and DBMS products supporting interactions and observations as alternative types of transactions"

Based on this definition and other considerations Microsoft is the leader edgin higher than the secodn Oracle. This is what you see in the Magic Quadrant. Microsoft shares the vision with others such as Oracle, SAP, AWS and IBM.

Here is copy of the Quadrant:



Detailed report here to download after registering:
https://info.microsoft.com/gartner-odbms-magic-quadrant-register.html?ls=website

Sunday, October 29, 2017

What is Power BI embedded?

If you are an app developer and you want to bring in the capabilities of Power BI into your app then Power BI Embedded is for you. It is an offer to present the analytic\visualization power of PowerBI as Software as a service.

Power BI Embedded simplifies this process to quickly create apps with great looking visuals. Easy data exploration capabilities support making quick and timely, informed decisions. What makes this possible are the use of Power BI APIs.

In order to develop powerful apps you will be using familiar tools like:

The Azure Portal
Visual Studio Code
Power BI Desktop

Using Power BI's API you can develop using any language.

Well here is a video that explains it all:



How do you use the MAP in MS Excel?

Excel provides excellent, easy-to-use MAP element that can be inserted into an Excel spreadsheet. It is easily accessed from the Insert menu item as shown. Of course your data must have geographical data.


Let us get some data into a work sheet. Click create Data and use the option to get from the Web. Let us use some data from Wikipedia which in turn shows data from Indian census. Let us use the Basic option.


Click OK.


You will get the next window that you are accessing the web content anonymously.

Click Connect. The connector starts connecting to the Demographics_of_India source.


When the connection succeeds you can access a bunch of data that comes into the Navigator pane of  a pop-up window as shown.


There is a wealth of data to be analyzed in the Navigator. We just choose the one shown, ‘Population between age 0-6 by state/union territory. This displays the data in the Table View pane as shown above.

Click Load to load the data into your Excel application in Sheet 1 as shown.


Excel has this nice feature of displaying the Recommended charts as you begin to insert a chart. It also shows how to do it as shown next. When you choose the whole data and try to bring in a Map, you will sent to BING (map provider) because BING  has the right map to provide and of course you need to accept their terms. When you accept, Map of the World comes up which now focuses on India as shown in the next window.


Note that it has already added the Min and Max values of the data. Clicking any particular map region shows the data for that region.


Now you can pretty up the map with all sorts of details using the Chart Elements.


Don't you think that was easy as 1-2-3?







Wednesday, October 18, 2017

How do you validate data in MS Excel?

For a range of cells that you choose, you can create criteria that must be satisfied for entering data into those cells. Any data outside the criteria will be flagged as an error.

Highlight a number of cells in a column and click the Data Validation toolbar item shown


As seen here a few cells have been highlighted and the Data Validation clicked. This will bring up the Data Validation form as shown. I set up the criteria as shown here allowing whole numbers between 1 and 200.


 Here attempting to enter 30.5 in one of the highlighted cells brings up this error message.


 Here entering a number greater than 200 brings up the message.

 You could also tailor the error message instead of the default one seen above by configuring the Error Alert tab as shown.

 Now you will see your custom message as shown.


This is the Office version used in this post.


What is the fix for the error message, 'Printer is offline'?

There could be many reasons for this error. I have a Canon MX430 series printer.
I could print earlier to this printer via Wi-Fi connection and probably after a Windows Update (usual suspect for something that breaks) I started getting the message, "Printer is offline". I know that the Printer is on and is connected to Wi-fi. Then what is the problem. The device troubleshooter in Windows 10 Professional could not fix it.

I tried to add the printer and to my dismay, I could not find the printer to add.
Then I tried to manually install following these steps.



Voila, I regained by ability to print to this printer. Do you know what? The Add Printer dialog find a networked printer. I am not what the code behind this search printers.
 

Sunday, October 15, 2017

What is the function SUMIF()?

The SUM() function in MS Excel adds up all values in a column as shown. You can indiate the range and hit the sigma function in the menu.

=SUM(A1:A6)


The SUMIF() function is a conditional sum function. In the example show here, the sum takes into value only those values greater than 100.

=SUMIF(A1:A6,">100")

The SUMIF() function is a conditional sum function. In the example show here, the sum takes into value only those values greater than 100.

=SUMIF(A1:A6,">100")


How do the Dubai fonts look like?

A recent Office 365 message referred to a new font in MS Excel called Dubai. The initial design of Dubai font family is supposed to consist of 4 styles:

Dubai Light,
Dubai (regular),
Dubai Medium and
Dubai Bold,
supporting both western European languages as well as the major languages that use the Arabic script.

Things to know about the Dubai font family:
Avoid using italics with any of the Dubai fonts, as we've not yet included true italic styles of the fonts.

The Dubai (regular) style is suited for body text and is “linked” to the Dubai Bold font. To apply the bold effect just select your text and choose bold from the Font group on the ribbon.

Because the Dubai fonts are designed for setting both Arabic and western European languages together, the space between lines is quite generous. You can tighten up this spacing by selecting Line Spacing Options from the Paragraph ribbon menu.

The font automatically installs in Word, Outlook, Excel and PowerPoint. If you'd like to have the font available to you in other programs on Windows like Microsoft Project, or third-party programs you'll need to go to download the Dubai Font Pack from the Microsoft Download Center.

In MS Excel I could find only the following:


This is in MS Word. Dubai Medium is missing and it is the same as Calibri.


In MS Word there were only two.

More here:
https://support.office.com/en-us/article/Using-the-Dubai-Font-in-Microsoft-Office-C862DF16-AE0D-46D9-B117-AA3F41F9706E

Download font from here:

https://www.dubaifont.com/download (Opentype TTF, OpenType CFF, TTF, EOT, WOFF and WOFF2)

History behind the font:
https://www.dubaifont.com/story

Saturday, October 14, 2017

Can I communicate with Lumia 935 with my iPhone6 using Bluetooth?

Microsoft Lumia 935 can pair with the iPhone6 but the Lumia 935 is not seen on iPhone6.

On Microsoft Lumia 935


On iPhone6


On Lumia 935 iPhone6 seems to be paired as well. Trying to send a photo using Bluetooth from Lumia 935 displays a message that the destination does not support the feature.


Thursday, October 12, 2017

Which Bitcoin wallet is good for Windows OS?

There are lots of options and that will depend on the device you will be using (Desktop, web, Smartphone, etc.). For Windows phones there is just one option-

Coin.space

But if you are transacting from desktop there are more options.

I may go for Electrum, a personal choice.

There are lot more options for iPhone and Android phones.

Wednesday, October 11, 2017

What is a DASD?

DASD is an acronym for Direct Access Storage Device. DASD is a secondary storage device (fixed and removable) where each physical record has a discrete storage location. The acronym was coined by IBM to designate disks, magnetic drums and data cells which also includes optical disc drives.

DASDs can be accessed,
Directly
Sequentially
Using Index
Randomly

Hard Disk Drives (HDD) are DASDs.

Tuesday, October 10, 2017

How do you capture screenshots with Firefox?

Firefox screen shots - an application to capture sections or whole Web pages is still in BETA, but you can give it a try. I have version 56.0(32bit).

The Firefox browser has a new menu item.



 It is very easy to use. Let us say, I open this page:

I get to see today's Bing page as shown:


I used a desktop app called lightshot to capture this image.

Now let me use the Firefox Screenshot menu to capture a part of this web page. In order to describe I will be capturing the steps again using lightshot.

Click on the menu item (you will see Firefox Screenshots when you hover the item). You get this intro screen.

You just hit the right pointing arrow to get to the next page of this wizard.


Click again the arrows.



Here click on this page and select the region as shown


Click Save. It gets saved to the cloud.


Click Download and it gets into your Downloads folder as shown.



Well it can only capture anything on the Firefox browser. Useful but other tools do exist.

Monday, October 9, 2017

How do you create a moving average using MS Excel?

You could use the Data Analysis menu item on your Excel application to find moving averages. Moving averages are technical indicators of data trend.

In order to use data analysis (regression, sampling, moving average,etc.) with
MS Excel you should have the Data Analysis menu item on your Excel ribbon.
You can do it longhand (which happens to be easier) as well.

If you do not find the Data Analysis menu item in your MS Excel ribbon you should bring in the Analysis ToolPak add-in as described here.
http://hodentekhelp.blogspot.com/2017/09/how-do-you-get-data-analysis-menu-item.html

Using Data Analysis Menu item:

Here is the data for which I want to find the moving average. This the Microsoft closing stock prices for the period shown. Only a part of the data is shown.


You click Data Analysis menu item on the ribbon to display the tasks that you can carry out.


Click on Moving Averages as the bottom of the menu. The following is displayed.





You insert the Input Range (the range that contains data for which you need the moving average. Output Range is where you want the moving average. You can also enable Chart Output as well as Standard Errors. The Interval is the moving average (200day, 100 day etc)

For the data in the sheet shown previously these are as follows:



Click OK to display the chart with data and the 7 day (there are only data for 30 days) moving average as shown.


The X-axis is the period and Y_Axis are the data and the moving average. Here are the 7day and 15 days moving averages.




Moving averages are used sometimes in buy/sell decisions of stocks. 200 Day moving average is often used. I think MSFT is a good buy.

A technical indicator compiled as a statistical series of a security's closing prices throughout 200 consecutive trading days. A 200-day moving average is designed to discover changes in a trend. Generally, a moving average is superimposed on a stock's line chart. If the stock price penetrates the moving average on the upside after a downward trend, the penetration is a signal to buy. But if the stock price penetrates the moving average on the downside following an upward trend, the penetration is a bearish sign.