Microsoft Excel - Functions

By Unknown -
Microsoft Excel is one of the finest tools that I have worked ever to store and analysis the data. It gives lot of different features and options to show the data visually.  I have got opportunity to analyses the data and generate the reports using MS-Excel and this tool is really add one skill to my data warehousing/BI skills.
Here I would like to explore some of the functionality of this great tool and I will be giving the tips in my next posts also.

We will discuss two functions here LARGE and VLOOKUP


LARGE
This is used to find the values of the large N files. In Fig 1, there are two fields mentioned in the figure, one is value field and the other one is name field. Here if we can find top 5 values using large function



Now we can look at the VLOOKUP function with the same dataset. we can find the name of the values selected by applying the formula =VLOOKUP(C2,A$2:B$17,2,0). Here C2 is the value searched in the data set F2:G17 and once the value searched it will pick the 2nd field value from the found value. Third option in the VLOOKUP function is to search exact value or the approximate match.
    Result

Comments

Post a Comment

Popular posts from this blog

Microsoft BI Implementation - Cube back up and restore using XMLA command

By -
Image
Current BI implementation require to deploy the cube in multiple location to make it available  locally in each location to improve the performance. Cube development happening in one location say X but since the company is having presence in around the world and so business users sits in different locations X, Y and Z. Internet connectivity between X, Y and Z are not good and accessing the cube deployed in X from Y and Z is very slow and it impact the end user productivity. To overcome this issue, cube has to be deployed in Y and Z locally and upload the connection in their local SharePoint as mentioned below.  Fig 1 Now let see how we can automate all these process from taking back up of the cube database to restore it in each location. Follow the below mentioned steps to automate back up and restore of cube database. - select the back up option by right click on the cube database to be backed up and generate the XMLA script after providing necessary proper
Read more

Databricks - incorrect header check

By -
This post I would like to show you how we can fix the problem of " Incorrect header check " received while fetching the data from hive table. Actual message " SparkException: Job aborted due to stage failure: Task 0 in stage 63.0 failed 4 times, most recent failure: Lost task 0.3 in stage 63.0 (TID 3506, 10.1.1.7, executor 28): java.io.IOException: incorrect header check.   at org.apache.hadoop.io.compress.zlib.ZlibDecompressor.inflateBytesDirect(Native Method)     at org.apache.hadoop.io.compress.zlib.ZlibDecompressor.decompress(ZlibDecompressor.java:221)     at org.apache.hadoop.io.compress.DecompressorStream.decompress(DecompressorStream.java:82)     at org.apache.hadoop.io.compress.DecompressorStream.read(DecompressorStream.java:76)     at java.io.InputStream.read(InputStream.java:101) " To better understand the scenario, let me explain how the data got loaded in the hive table. we are storing the data from source systems to RAW layer. H
Read more