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.

Difficulty

Objective

You have a data set and you want to determine if the data contains a particular value. For example, you have a table containing sales transaction data and you want to know if a certain item was sold based on the SKU number. You know how to do this in Excel using the IF and OR functions, and you would like to perform the same operations in 1010data.

Excel solution

There are multiple ways to determine if your data set contains a specified value using Excel. One versatile method uses the functions IF and OR. The OR function serves as the logic test for the IF function. It compares a single value to a column or an array of values to determine if there is any match. In this case, you enter a SKU number into the cell Q5 and the following equation returns, Found, if it is present in the Sku column, and Not Found, if it is not.

=IF(OR(Q5=(E2:E100001)),"Found","Not Found")

The image below shows the Sales Detail data imported from 1010data and the results of the above equation.

However, using Excel has its drawbacks. If you try to import the Sales Detail table from 1010data in its entirety, you will receive the following error message Query results in 4,481,814 rows, only 1,048,576 rows available in worksheet at specified result destination. Using 1010data, you can search for a specific value using the whole table instead of just a subset.

1010data GUI solution

Similar to Excel, there are multiple ways to determine if a data set contains a specific value using 1010data's GUI. Mirroring the solution from Excel, you can populate a cell, or in 1010data's case, a column of cells, with text stating if the value is "Found" or "Not Found." Clicking on Columns > Create Computed Column, will open the following dialog.

You can name the column by populating the Column Name and Column Heading text fields, and then within the Value Expression text field, the formula to determine if the value is present in the table is entered. The if(C1;R1;C2;R2;...;D) function is used so that if the SKU is present, the new column is populated with "Found" and otherwise, "Not Found."

Alternate 1010data GUI solution

Another solution that is perhaps the simplest and fastest way is to perform a selection. However, this solution will transform the table to display only the data containing the selected value. To open the following dialog, click Rows > Select Rows from the 1010data menu.

Using the drop-down menus in the dialog, you select SKU and has the value(s). Then, you enter the SKU you would like to search for in the text box. If the SKU is found in the table, a message will appear that displays how many rows contain that value. If the value is not found, you will receive a message that displays No matches., as can be seen in the dialog below.

1010data Macro Language solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<willbe name="val_found" value="if(sku=294118;'Found';'Not Found')”/>

To obtain a worksheet that looks similar to the Excel solution, using the Macro Language, you can create a column that will be conditionally populated with text. <willbe> creates the column and in the value attribute, the function if(C1;R1;C2;R2;...;D) is used to supply the system with the two options, "Found" and "Not Found" based on the entered SKU.

Alternate 1010data Macro Language solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<sel value="sku=247560"/>

A selection statement can be used within the Macro Language to produce the same results as the alternate GUI solution. If the SKU you enter is present in the table, it will select only the rows that contain that SKU. If it is not present, a similar message will appear, stating No rows selected.