Category: Big Data & Analytics

Aug 10

Splunk – Subseraching

Splunk – Subsearching ”; Previous Next Subsearch is a special case of the regular search when the result of a secondary or inner query is the input to the primary or outer query. It is similar to the concept of subquery in case of SQL language. In Splunk, the primary query should return one result which can be input to the outer or the secondary query. When a search contains a subsearch, the subsearch is run first. Subsearches must be enclosed in square brackets in the primary search. Example We consider the case of finding a file from web log which has maximum byte size. But that may vary every day. Then we want to find only those events where the file size is equal to the maximum size, and is a Sunday. Create the Subsearch We first create the subsearch to find the maximum file size. We use the function Stat max with the field named bytes as the argument. This identifies the maximum size of the file for the time frame for which the search query is run. The below image shows the search and the result of this subsearch − Adding the Subsearch Next, we add the subsearch query to the primary or the outer query by putting the subsearch inside square brackets. Also the search clause is added to the subsearch query. As we see, the result contains only the events where the file size is equal to the max file size found by considering all the events, and the event day is a Sunday. Print Page Previous Next Advertisements ”;

Aug 10

Plotly – Distplots, Density Plot & Error Bar Plot

Plotly – Distplots Density Plot and Error Bar Plot ”; Previous Next In this chapter, we will understand about distplots, density plot and error bar plot in detail. Let us begin by learning about distplots. Distplots The distplot figure factory displays a combination of statistical representations of numerical data, such as histogram, kernel density estimation or normal curve, and rug plot. The distplot can be composed of all or any combination of the following 3 components − histogram curve: (a) kernel density estimation or (b) normal curve, and rug plot The figure_factory module has create_distplot() function which needs a mandatory parameter called hist_data. Following code creates a basic distplot consisting of a histogram, a kde plot and a rug plot. x = np.random.randn(1000) hist_data = [x] group_labels = [”distplot”] fig = ff.create_distplot(hist_data, group_labels) iplot(fig) The output of the code mentioned above is as follows − Density Plot A density plot is a smoothed, continuous version of a histogram estimated from the data. The most common form of estimation is known as kernel density estimation (KDE). In this method, a continuous curve (the kernel) is drawn at every individual data point and all of these curves are then added together to make a single smooth density estimation. The create_2d_density() function in module plotly.figure_factory._2d_density returns a figure object for a 2D density plot. Following code is used to produce 2D Density plot over histogram data. t = np.linspace(-1, 1.2, 2000) x = (t**3) + (0.3 * np.random.randn(2000)) y = (t**6) + (0.3 * np.random.randn(2000)) fig = ff.create_2d_density( x, y) iplot(fig) Below mentioned is the output of the above given code. Error Bar Plot Error bars are graphical representations of the error or uncertainty in data, and they assist correct interpretation. For scientific purposes, reporting of errors is crucial in understanding the given data. Error bars are useful to problem solvers because error bars show the confidence or precision in a set of measurements or calculated values. Mostly error bars represent range and standard deviation of a dataset. They can help visualize how the data is spread around the mean value. Error bars can be generated on variety of plots such as bar plot, line plot, scatter plot etc. The go.Scatter() function has error_x and error_y properties that control how error bars are generated. visible (boolean) − Determines whether or not this set of error bars is visible. Type property has possible values “percent” | “constant” | “sqrt” | “data”. It sets the rule used to generate the error bars. If “percent”, the bar lengths correspond to a percentage of underlying data. Set this percentage in `value`. If “sqrt”, the bar lengths correspond to the square of the underlying data. If “data”, the bar lengths are set with data set `array`. symmetric property can be true or false. Accordingly, the error bars will have the same length in both direction or not (top/bottom for vertical bars, left/right for horizontal bars. array − sets the data corresponding the length of each error bar. Values are plotted relative to the underlying data. arrayminus − Sets the data corresponding the length of each error bar in the bottom (left) direction for vertical (horizontal) bars Values are plotted relative to the underlying data. Following code displays symmetric error bars on a scatter plot − trace = go.Scatter( x = [0, 1, 2], y = [6, 10, 2], error_y = dict( type = ”data”, # value of error bar given in data coordinates array = [1, 2, 3], visible = True) ) data = [trace] layout = go.Layout(title = ”Symmetric Error Bar”) fig = go.Figure(data = data, layout = layout) iplot(fig) Given below is the output of the above stated code. Asymmetric error plot is rendered by following script − trace = go.Scatter( x = [1, 2, 3, 4], y =[ 2, 1, 3, 4], error_y = dict( type = ”data”, symmetric = False, array = [0.1, 0.2, 0.1, 0.1], arrayminus = [0.2, 0.4, 1, 0.2] ) ) data = [trace] layout = go.Layout(title = ”Asymmetric Error Bar”) fig = go.Figure(data = data, layout = layout) iplot(fig) The output of the same is as given below − Print Page Previous Next Advertisements ”;

Aug 10

QlikView – Sheet and Objects

QlikView – Sheet and Objects ”; Previous Next Every QlikView document is made of at least one worksheet called Main. We can add more sheets, which are like many pages of the same QlikView document. Sheets help us display multiple data formats like – multiple charts or multiple tables. Each sheet can contain various sheet objects. In addition, sheets can be rearranged using Promote Sheet/Demote Sheet option and can be removed from the QlikView document using Remove Sheet option. Sheet Properties Sheets have various properties, which can be set to customize the sheets. For example, we can set the name of the sheets and its colors. Right click anywhere in the sheet and choose the Properties option. Then choose the following properties. Sheet Settings → Color. − This will set the background colour of the Sheet. Tab Settings → Custom Colors. − This will set the color for the Tab where the Sheet name appears. Title. − This will set the name of the Sheet. Creating Sheet Objects Sheet Objects are the QlikView data elements that are embedded in the sheet. They display the data that is loaded into the QlikView”s memory. Each sheet object is tied to a data source and one or more of its columns. Sheet Objects are created from the layout menu as shown below. Using Sheet Objects Sheet Objects display the data from a data source and all the objects in a sheet are associated with each other. Let”s create a List Box and a Multi Box and see this association on action. Creating List Box The List box displays data from a column of a table available in QlikView memory. Choose the option List Box from the Add Sheet Objects option and set the properties as given below. Creating Multi Box A Multi Box represents data from multiple columns from a table. Choose the option Multi Box from the Add Sheet Objects option and set the properties as shown below. On completing the above given steps, the following window appears which shows both the sheet objects. Association between Sheet Objects We can see how the sheet objects are linked to each other by choosing the one option from the Multi Box, which highlights the associated row in the List Box. Let us choose “Diapering” under the Product Category drop down list in Multi Box. The window shown below appears. Print Page Previous Next Advertisements ”;

Aug 10

Splunk – Reports

Splunk – Reports ”; Previous Next Splunk reports are results saved from a search action which can show statistics and visualizations of events. Reports can be run anytime, and they fetch fresh results each time they are run. The reports can be shared with other users and can be added to dashboards. More sophisticated reports can allow a drill down function to see underlying events which create the final statistics. In this chapter, we will see how to create and edit a sample report. Report Creation Report creation is a straight forward process where we use the Save As option to save the result of a search operation choosing the RepSave Asorts option. The below diagram shows the option. By clicking on the Reports option from the dropdown, we get the next window which asks for additional inputs like the name of the report, the description and choosing the time picker. If we choose the time picker, it allows the time range to be adjusted when we run the report. Below diagrams show how we fill the required details and then click save. Report Configuration After clicking save to create the report in the above step, we get the next screen asking for configuring the report as shown below. Here, we can configure the permissions, scheduling the report, etc. We also get an option to go to the next step and add the report to a dashboard. If we click on View in the above step, we can see the report. We also get configuration options after the report is created. Modifying Report Search Option While we can edit the permissions, schedule, etc., sometimes we need to modify the original search string. This can be done by choosing the Open in Search option as given in the above image. That will open the original search option again which we can be edited to a new search. Refer to the below image − Print Page Previous Next Advertisements ”;

Aug 10

QlikView – Resident Load

QlikView – Resident Load ”; Previous Next QlikView can load data from tables already existing in its RAM, which is already processed by a script. This requirement arises when you want to create a table deriving data from an already existing table in the same script. Please note that both the new table and the existing table should be in the same script. Creating the Load Script Open the script editor (or use Control+E) and mention the following script. Here we create an inline table named Regions with sales data for different regions. Then we create another table named Total to calculate the total sales by Region Names. Finally we drop the table Regions, as in this .qvw file we only need the table named Total for data analysis. Table Box Data On creating a Table Box Sheet Object, we see the data that is read from the resident data load option. Print Page Previous Next Advertisements ”;

Aug 10

QlikView – Text Object

QlikView – Text Object ”; Previous Next QlikView text Object is used to show some descriptive information about the QlikView report being displayed. It can also show calculations based on certain expressions. It is mainly used for displaying nicely formatted information using colors and different font types in a box separately from the other Sheet Objects. Input Data Let us consider the following input data, which represents the sales figure of different product lines and product categories. Product_Line,Product_category,Value Sporting Goods,Outdoor Recreation,5642 Food, Beverages & Tobacco,2514 Apparel & Accessories,Clothing,2365 Apparel & Accessories,Costumes & Accessories,4487 Sporting Goods,Athletics,812 Health & Beauty,Personal Care,6912 Arts & Entertainment,Hobbies & Creative Arts,5201 Arts & Entertainment,Paintings,8451 Arts & Entertainment,Musical Instruments,1245 Hardware,Tool Accessories,456 Home & Garden,Bathroom Accessories,241 Food,Drinks,1247 Home & Garden,Lawn & Garden,5462 Office Supplies,Presentation Supplies,577 Hardware,Blocks,548 Baby & Toddler,Diapering,1247 Baby & Toddler,Toys,257 Home & Garden,Pipes,1241 Office Supplies,Display Board,2177 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the “Table Files” option from the “Data from Files” tab and browse for the file containing the above data. Edit the load script to add the following code. Click “OK” and press “Control+R” to load the data into the QlikView”s memory. LOAD Product_Line, Product_category, Value FROM [C:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Table Box For the above data, let us create a Table Box , which will show the data in a tabular form. Go to the menu Layout → New Sheet Object → Table Box and choose the column as shown below. Click Apply and then OK to finish creating the Table box. The following screen appears. Creating Text Object For the above data, let us create a Text Object. Go to the menu Layout → New Sheet Object → Text Object as shown below. Creating Text Object Expression On the text box created above, right click and choose properties. Then enter the content to be displayed on the Text Object in the Text box under the General tab as shown below. Choosing the Background Color The background color of the Text Object can be set using the background option in the General tab. The Text Object The final Text Object is shown below. If we click on some Product Line to filter it, then the content in the Text Object changes accordingly to reflect the new values. Print Page Previous Next Advertisements ”;

Aug 10

QlikView – Preceding Load

QlikView – Preceding Load ”; Previous Next QlikView Preceding load is a load type in which we use a load statement, which takes the columns of another load statement present in the same script. The data read by the first Load statement, which is at the bottom of the script editor window and then used by the load statements above it. Load Script The below given screen shot shows the script for data, which is loaded as Inline data and then the max function is applied to one of the columns. The load statement at the bottom makes the data available in QlikView”s memory, which is used by the second load statement above the first load statement. The second load statement applies the max function with group by clause. Table Box Data On creating a Table Box Sheet Object, we see the data that is read from the Inline data load option. Print Page Previous Next Advertisements ”;

Aug 10

QlikView – Peek Function

QlikView – Peek Function ”; Previous Next The peek() function in QlikView is used to fetch the value of a field from a previous record and use it in calculations. Input Data Let us consider the monthly sales figure as shown below. Save the data with file name monthly_sales.csv. Month,Sales Volume March,2145 April,2458 May,1245 June,5124 July,7421 August,2584 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from for the file containing the above data. Edit the load script to add the following code. Click OK and click Control+R to load the data into QlikView”s memory. LOAD Month, [Sales Volume], peek(”Sales Volume”) as Prevmonth FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above script. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the csv file in the QlikView Table Box as shown below. Also set the sort order as shown below to get the result in the same order of the field Month as it is in the source. On completing the above steps and clicking Finish, we get the Table box showing the data as given below. Using peek() Function in Calculations The peek() can be used in calculations involving other columns. Let us display the percentage change for sales volume for each month. The following script achieves this result. LOAD Month, [Sales Volume], peek(”Sales Volume”) as Prevvolume, (([Sales Volume]-peek(”Sales Volume”)))/peek(”Sales Volume”)*100 as Difference FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above script. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Print Page Previous Next Advertisements ”;

Aug 10

Qlikview – Functions

QlikView – Functions ”; Previous Next QlikView has many built-in functions, which are available to be applied to data that is already available in memory. These functions are organized into many categories and the syntax of the function appears as soon as it is selected. We can click on the Paste button to get the expression into the editor and supply the arguments. Create Table Box Create a Table Box by following the menu as shown in the screen shot given below. On completing the above given step, we get a window to show the Calculation condition at the bottom left. List of Functions Click on the button next to calculation condition and go to the Function tab. It shows the list of functions available. On choosing String from the functions category, we can see only few functions, which take a string as an argument. In the next chapters, we will see the use of many important functions. Print Page Previous Next Advertisements ”;

Aug 10

QlikView – Excel file

QlikView – Excel Files ”; Previous Next QlikView accepts Excel spreadsheet for data analysis by simple drag and drop action. You need to open the QlikView main window and drag and drop the excel file into the interface. It will automatically create the sheet showing the excel data. Select the Excel file Keep the main window of QlikView open and browse for the excel file you want to use. Select a Data Source On dropping the excel file into the main window, the File wizard appears. The File Type is already chosen as Excel. Under Labels, choose Embedded Labels. Click “Next step” to proceed. Load Script The Load script appears which shows the command that loads the data into the QlikView document. This command can be edited. Now, the Excel wizard prompts to save the file in the form of *.qvw file extension. It asks to select a location where you need to save the file. Click “Next step” to proceed. Now it is time to see the data that is loaded from the Excel file. We use a Table Box sheet object to display this data. Create Table Box The Table Box is a sheet object to display the available data as a table. It is invoked from the menu Layout → New Sheet Object → Table Box. On clicking Next, we get the option to choose the fields from the Table Box. You can use the Promote or Demote buttons to rearrange the fields. Table Box Data On completing the above step, the Table Box Sheet Object appears which shows the data that is read from the Excel file. Print Page Previous Next Advertisements ”;