Home
Analogues
This section contains 1010data analogues for Excel and SQL users.
1010data for Excel users
1010data can solve the same problems that you are used to solving in Excel in one or more ways. Sometimes there are direct translations between the two, and sometimes a unique solution is needed in order to arrive at the same result.
Calculations
Returning values based on location
G_functions can return a selection of data based on the location of a specified value, similar to using MATCH and OFFSET in Excel.

Analogues
This section contains 1010data analogues for Excel and SQL users.
- 1010data for Excel users
  1010data can solve the same problems that you are used to solving in Excel in one or more ways. Sometimes there are direct translations between the two, and sometimes a unique solution is needed in order to arrive at the same result.
  - Introduction
  - Function comparisons
    You know how to use certain functions in Excel, but you want to know how to complete the same tasks using 1010data's functions. While some Excel functions have a direct match to a function in 1010data, others match to a combination of functions/operations. The following sections show some of the analogous functions between Excel and 1010data.
  - Calculations
    - Finding duplicates
      Sometimes tables contain duplicate entries and it can be necessary to locate and remove these duplicates.
    - Calculating the maximum and minimum values
      A single tabulation in 1010data can calculate the maximum and minimum values of a table.
    - Returning values based on location
      G_functions can return a selection of data based on the location of a specified value, similar to using MATCH and OFFSET in Excel.
    - Finding a value corresponding to the nth element
      Using the g_cumcnt function, you can find corresponding information related to a specific occurrence of a value, similar to using COUNTIF and VLOOKUP in Excel.
    - Finding corresponding data in different tables
      You can use a link and select to find information on a specified value contained in a separate table. This operation is similar to using LOOKUP and VLOOKUP to find corresponding information in a different worksheet.
    - Summing every other value
      Using the mod function makes it simple to sum every other value both in Excel and 1010data.
    - Summing over multiple conditions
      G_functions allow you to calculate multiple sums based on data meeting certain criteria, similar to SUMIFS in Excel.
    - Splitting and concatenating strings
      Using string manipulations can convert a column containing names to a column containing initials.
    - Determining if a data set contains a specified value
      There are various ways to determine if a specific value is contained within a data set, similar to using IF and OR in Excel.
    - Filtering and sorting a table
      Filtering and sorting the data in a table based on specified criteria can be useful for both completing aggregations and viewing the data.
    - Determining the differences between two data sets
      You can use 1010data to determine the number of differences between two tables, similar to using COUNTIF in Excel.
    - Computing multiple cumulative sums
      Cumulative sums can be useful to distinguish individual sums of several different groups.
  - Visualizations
- 1010data for SQL users
  1010data users have backgrounds using different programming languages, databases, and analytical tools. One of the most widely used tools for data is SQL. This section of the 1010data Cookbook was created to help the users with SQL background learn how to complete tasks in 1010data. Each analogue will describe a problem that the user knows how to complete in SQL, and then show how to solve the problem in 1010data.

Returning values based on location

G_functions can return a selection of data based on the location of a specified value, similar to using MATCH and OFFSET in Excel.

Difficulty

Objective

You have a data table and you would like to return a collection of values surrounding a specific data point. For example, you have a record of daily snowfall and would like to know the entries for the week prior and the week after the largest recorded snowfall. You know how to do this in Excel by using the MAX, MATCH, and OFFSET functions, and you would like to know how to perform the same operations using 1010data.

Excel solution

The OFFSET function returns a cell or number of cells surrounding a specified position. To obtain all of the arguments for OFFSET, MATCH is used in combination with MAX to find the position of the cell that contains the highest snowfall for the recorded data. Then the following array formula is used to find the information for the surrounding days.

=OFFSET(D1,MATCH(MAX(D:D),D:D,0)-4,-2,7,3)

Your Excel worksheet should look similar to the image below.

Because the formula is an array formula, you need to select multiple cells in which to enter the formula in, because a single cell can only display one value. This can be difficult when selecting large amounts of data. Using 1010data, a single command can select data of varying size.

1010data Macro Language solution

<base table="pub.demo.weather.wunderground.observed_daily"/>
<sel value="zipcode=01031"/>
<willbe name="test" value="g_hi(zipcode;;snowfalli)"/>
<sel value="between(i_;g_position(zipcode;;;snowfalli;test)-7;
g_position(zipcode;;;snowfalli;test)+7)"/>
<colord cols="zipcode,date,snow,snowfalli"/>

In order to select the data that surrounds the data point with highest snowfall, you first need to find the highest snowfall value, using g_hi(G;S;X), and populate a computed column with that value. This computed column is needed because the function g_position(G;S;O;X;Y) compares values in specified columns to find the row containing that value.

i_ is a system variable that denotes a specific row in the table. Using the function between(X;Y;Z) you can select rows that are contained within a range based on the row position of the max snowfall, plus or minus seven rows. This combination of operations produces the worksheet seen below.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

g_position(G;S;O;X;Y)

between(X;Y;Z)