Home
Analogues
This section contains 1010data analogues for Excel and SQL users.
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.
Function comparisons
Logic Functions

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.
- 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.
  - Introduction
  - Function comparisons
  - Counting occurrences
    Based on specified groups in your data, you can determine how many times pairs of values occur together.
  - Counting based on conditions
    You can determine how many times a specified value occurs in a table based on predetermined and conditional requirements.
  - Calculating group specific averages
    Instead of calculating one overall average, you can calculate smaller averages based on criteria specific groups.
  - Selecting based on rank
    You can select values based on certain criteria, for example if their rank puts them in a certain range.
  - Time comparison analysis by groups
    You can compare aggregated data for two different time periods, even if that data is contained in the same column.
  - Performing an inner join
    An INNER JOIN in SQL is similar to a link and select in 1010data.
  - Performing a left or right join
    With some manipulation, a simple link in 1010data can produce the same results as a LEFT JOIN or a RIGHT JOIN in SQL.
  - Performing a union
    A UNION in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. During this union, all duplicates are removed.
  - Performing a union all
    A UNION ALL in SQL combines the rows from one table with the rows from another, pending both tables contain the same number of columns with matching data types, similar to a merge in 1010data. With this variation of UNION all rows, including duplicates, are retained in the final worksheet.
  - Denormalizing tables
    In SQL it is very common to have smaller tables that are used as reference tables to a larger data set. This allows the data to be stored easier and accessed faster. However, if you want to perform any calculations that involve these reference tables in addition to your main table, you must first combine them, or denormalize them.
  - Transposing a table
    In order to transpose a table using SQL, multiple operations are required. Using 1010data, you can transpose a table with one basic operation.

Logic Functions


SQL Function	1010data Function	Description
`IF(expr1,expr2,expr3)`	`if(C1;R1;C2;R2;...;D)`	For SQL, if expr1 is true, then expr2 is returned, otherwise expr3 is returned. For 1010data, the value corresponding to the first condition that evaluates to true is returned; if no conditions are true, the specified default value is returned.
`IF(expr1,expr2,expr3)`	`case(X;V1;R1;V2;R2;...;D)`	For SQL, if expr1 is true, then expr2 is returned, otherwise expr3 is returned. For 1010data, the value corresponding to the case that a given value matches is returned; if no cases match, the specified default value is returned.
`IFNULL(expr1,expr2)`	`ifnull(X;Y)`	For SQL, if expr1 is NULL, then expr2 is returned, otherwise expr1 is returned. For 1010data, a specified value is returned when the value in a given column is N/A.
`NULLIF(expr1,expr2)`	`nullif(X;Y)`	For SQL, if expr1 equals expr2, NULL is returned, otherwise expr1 is returned. For 1010data, if the two given values match, N/A is returned.