Difference between revisions of "Manuals/calci/DGET"

From ZCubes Wiki
Jump to navigation Jump to search
 
(12 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
=DGET(database, field, criteria)=
+
<div style="font-size:30px">'''DGET (Array,Field,Criteria,IsGetRows)'''</div><br/>
−
 
+
*Where 'Array' is the range of cells that makes up the list or database
−
*Where 'database' is the range of cells that makes up the list or database
+
*'Field' shows which column is used in the function and  
−
 
+
*'Criteria' is the range of cells that contains the specified criteria.
−
*'field' shows which column is used in the function and  
+
**DGET(), extracts from a database a single record that matches the specified criteria.
−
 
 
−
*'criteria' is the range of cells that contains the specified criteria.
 
−
 
 
−
DGET() obtains a single value from the database that matches the specified criteria.
 
  
 
== Description ==
 
== Description ==
  
−
DGET(database, field, criteria)
+
DGET (Array,Field,Criteria,IsGetRows)
  
−
The function obtains a single value from the list or database that matches the specified condition.
+
The function obtains a single value from the list or database that matches a specified condition.
−
*If no matching records are found, Calci returns an #ERROR message.
+
*A 'Field' argument can be expressed as "Salary" (a column name) or 3(position of the column).  
−
*If more than one records are matching with the given criteria, Calci returns an #ERROR message.
+
*If no matching records are found, Calci returns an #N/A error message.
 +
*If more than one records are matching with the given criteria, Calci returns an #N/A error message.
 
*Criteria can be any range on the Calci with minimum of one column and at least one row indicating the condition.
 
*Criteria can be any range on the Calci with minimum of one column and at least one row indicating the condition.
 
*The Criteria columns should not overlap the list of data.
 
*The Criteria columns should not overlap the list of data.
  
 +
== Examples ==
 +
Consider the following table in ZCalci with Employee names, Age and Salary columns.
 +
<br>Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.
  
−
== Examples ==
 
 
<div id="2SpaceContent" class="zcontent" align="left">
 
<div id="2SpaceContent" class="zcontent" align="left">
  
 
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
−
| class="sshl_f " | '''Name'''
+
| class="sshl_f" |
 +
| class="sshl_f" | A
 +
| class="sshl_f" | B
 +
| class="sshl_f" | C
 +
| class="sshl_f" | D
 +
|- class="odd"
 +
| class="sshl_f" | 1
 +
| class="sshl_f" | '''Name'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Age'''
−
|- class="odd"
+
|- class="even"
 +
| class="sshl_f" | 2
 
| class="sshl_f" |
 
| class="sshl_f" |
 
| class="sshl_f" | &lt;40
 
| class="sshl_f" | &lt;40
−
| class="sshl_f" | &gt;5000
+
| class="sshl_f" | &gt;8000
−
| class="sshl_fSelectTD SelectTD " | &gt;32
+
| class="sshl_f" | &gt;32
−
<div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div>
+
|- class="odd"
−
|- class="even"
+
| class="sshl_f" | 3
 
| class="sshl_f" |
 
| class="sshl_f" |
−
| class="sshl_f " | &gt;30
+
| class="sshl_f" | &gt;30
 
| class="sshl_f" |  
 
| class="sshl_f" |  
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="odd"
+
|- class="even"
−
| class="sshl_f   " | '''Name'''
+
| class="sshl_f" | 4
 +
| class="sshl_f" | '''Name'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Age'''
 
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" | '''Salary'''
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="even"
+
|- class="odd"
 +
| class="sshl_f" | 5
 
| class="sshl_f" | John
 
| class="sshl_f" | John
 
| class="sshl_f" | 34
 
| class="sshl_f" | 34
 
| class="sshl_f" | 5500
 
| class="sshl_f" | 5500
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="odd"
+
|- class="even"
 +
| class="sshl_f" | 6
 
| class="sshl_f" | Bill
 
| class="sshl_f" | Bill
 
| class="sshl_f" | 35
 
| class="sshl_f" | 35
 
| class="sshl_f" | 6500
 
| class="sshl_f" | 6500
−
| class="sshl_f " |
+
| class="sshl_f" |
−
|- class="even"
+
|- class="odd"
 +
| class="sshl_f" | 7
 
| class="sshl_f" | Clark
 
| class="sshl_f" | Clark
 
| class="sshl_f" | 29
 
| class="sshl_f" | 29
 
| class="sshl_f" | 7000
 
| class="sshl_f" | 7000
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="odd"
+
|- class="even"
 +
| class="sshl_f" | 8
 
| class="sshl_f" | Bob
 
| class="sshl_f" | Bob
 
| class="sshl_f" | 45
 
| class="sshl_f" | 45
 
| class="sshl_f" | 9000
 
| class="sshl_f" | 9000
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="even"
+
|- class="odd"
 +
| class="sshl_f" | 9
 
| class="sshl_f" | Susan
 
| class="sshl_f" | Susan
 
| class="sshl_f" | 37
 
| class="sshl_f" | 37
 
| class="sshl_f" | 4000
 
| class="sshl_f" | 4000
 
| class="sshl_f" |
 
| class="sshl_f" |
−
|- class="odd"
+
|- class="even"
 +
| class="sshl_f" | 10
 
| class="sshl_f" | Jill
 
| class="sshl_f" | Jill
 
| class="sshl_f" | 45
 
| class="sshl_f" | 45
−
| class=" " | 8000
+
| class="ssh1_f" | 8000
 
| class="sshl_f" |
 
| class="sshl_f" |
 
|}
 
|}
  
  
−
=DGET()  :
+
=DGET(A4:C10,"Name",C1:C2)  : Extracts a single value from Name column in the range A4 to C10, for the cells with Salary>8000. <br>Returns '''Bob''' as a result.
−
=DGET()  :
+
 
−
=DGET()  :
+
=DGET(A4:C10,2,C1:D2)  : Extracts a single value from the second column (Age) in the range A4 to C10, for the cells with Salary>8000 and Age>32. <br>Returns '''45''' as a result.
 +
 
 +
=DGET(A4:C10,"Salary",A1:A3)  : Extracts a single value from Salary column in the range A4 to C10, for all cells from the Name column. <br>Returns '''#N/A''' error message as there are more than one matching results.
 +
 
 +
==Related Videos==
 +
 
 +
{{#ev:youtube|zJIAOt5BKYk|280|center|DGET}}
  
 
== See Also ==
 
== See Also ==
  
 +
*[[Manuals/calci/DCOUNT| DCOUNT]]
 
*[[Manuals/calci/DCOUNTA| DCOUNTA]]
 
*[[Manuals/calci/DCOUNTA| DCOUNTA]]
  
Line 90: Line 111:
 
*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions]
 
*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions]
 
*[http://en.wikipedia.org/wiki/Aggregate_function DGET]
 
*[http://en.wikipedia.org/wiki/Aggregate_function DGET]
 +
 +
 +
 +
*[[Z_API_Functions | List of Main Z Functions]]
 +
 +
*[[ Z3 |  Z3 home ]]

Latest revision as of 13:47, 24 August 2018

DGET (Array,Field,Criteria,IsGetRows)


  • Where 'Array' is the range of cells that makes up the list or database
  • 'Field' shows which column is used in the function and
  • 'Criteria' is the range of cells that contains the specified criteria.
    • DGET(), extracts from a database a single record that matches the specified criteria.

Description

DGET (Array,Field,Criteria,IsGetRows)

The function obtains a single value from the list or database that matches a specified condition.

  • A 'Field' argument can be expressed as "Salary" (a column name) or 3(position of the column).
  • If no matching records are found, Calci returns an #N/A error message.
  • If more than one records are matching with the given criteria, Calci returns an #N/A error message.
  • Criteria can be any range on the Calci with minimum of one column and at least one row indicating the condition.
  • The Criteria columns should not overlap the list of data.

Examples

Consider the following table in ZCalci with Employee names, Age and Salary columns.
Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.

A B C D
1 Name Age Salary Age
2 <40 >8000 >32
3 >30
4 Name Age Salary
5 John 34 5500
6 Bill 35 6500
7 Clark 29 7000
8 Bob 45 9000
9 Susan 37 4000
10 Jill 45 8000


=DGET(A4:C10,"Name",C1:C2) : Extracts a single value from Name column in the range A4 to C10, for the cells with Salary>8000.
Returns Bob as a result.

=DGET(A4:C10,2,C1:D2) : Extracts a single value from the second column (Age) in the range A4 to C10, for the cells with Salary>8000 and Age>32.
Returns 45 as a result.

=DGET(A4:C10,"Salary",A1:A3) : Extracts a single value from Salary column in the range A4 to C10, for all cells from the Name column.
Returns #N/A error message as there are more than one matching results.

Related Videos

DGET

See Also

References


Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/DGET&oldid=211231"