Create custom tables — sc_table

Define requests against the /table endpoint by providing URIs to databases, measures and fields. The URIs can be obtained using sc_schema_db(). See the Custom tables article for more details.

Usage

sc_table_custom(
  db,
  measures = c(),
  dimensions = c(),
  language = c("en", "de"),
  add_totals = TRUE,
  key = NULL,
  recodes = NULL,
  dry_run = FALSE
)

sc_recode(field, map = NULL, total = FALSE)

Arguments

db: The uid of a database. Must be of type DATASET
measures: A character vector of uids for measures. Each entry must be of type MEASURE, STAT_FUNCTION or COUNT.
dimensions: A character vector of dimensions for the cube. Can be either of type FIELD or type VALUESET. Those entries are referred to as fields in the parsed API response
language: The language to be used for labeling. "en" (the default) will use english. "de" uses german.
add_totals: Should totals be added for each classification field in the json request? Ignored if recodes is used.
key: (string) An API key. To display your key, call sc_browse_preferences().
recodes: One or more recodes that were generated via sc_recode(). If more than one recode is supplied, recodes should be concatinated with c().
dry_run: If TRUE, no request is sent to the API. Instead, type checks are performed and the json request is returned as a string. Defaults to FALSE.
field: An uid of a classification field to be recoded. The provided uid should also be passed in the dimensions parameter of sc_table_custom().
map: A list of ids for values (type VALUE) This can also be a nested list if items should be grouped. See examples
total: Add totals to the field? If map is provided, the totals will correspond to the filtered data.

Functions

sc_recode(): creates a recode object which can be used for the recode parameter of sc_table_custom()

Schema objects in parameters

it is possible to pass sc_schema objects (usually generated by sc_schema_db()) instead of ids in sc_table_custom() and sc_recode(). If provided, the schema objects will be converted into ids via $id.

Error handling

Unfortunately, the API gives fairly vague error messages in case a custom table request is ill defined. For this reason, sc_table_custom() applies some simple heuristics and throws warnings if inconsistencies in the provided parameters are recognized. The following conditions are currently checked

the parameter db is of type DATABASE
all entries in measures are of type MEASURE, COUNT or STATFN
all entries in dimensions are of type VALUESET or FIELD
all entries in field are of type VALUESET or FIELD
all entries in map are of type VALUE
all fields in recodes are also present in dimensions
the first two arguments of sc_recode() are consistent, i.e. if the provided VALUEs belong to the VALUESET/FIELD

Examples

sc_table_custom("str:database:detouextregsai")
#> Accomodation statistics as of 1974 according to seasons
#> 
#> Database: detouextregsai (STATcube)
#> Measures: Nights spent
#> Fields: Season/Tourism Month <2>
#> 
#> Request: [2022-09-02 12:21:00]
#> STATcubeR: 0.5.0.1

sc_table_custom(
  "str:database:detouextregsai",
  dimensions = "str:field:detouextregsai:F-DATA1:C-SDB_TIT-0"
)
#> Accomodation statistics as of 1974 according to seasons
#> 
#> Database: detouextregsai (STATcube)
#> Measures: Nights spent
#> Fields: Season/Tourism Month <47>
#> 
#> Request: [2022-09-19 14:40:09]
#> STATcubeR: 0.5.0.1

sc_table_custom(
  db = "str:database:detouextregsai",
  measures = c(
    "str:statfn:detouextregsai:F-DATA1:F-ANK:SUM",
    "str:measure:detouextregsai:F-DATA1:F-UEB"
  ),
  dimensions = c(
    "str:field:detouextregsai:F-DATA1:C-SDB_TIT-0",
    "str:valueset:detouextregsai:F-DATA1:C-C93-2:C-C93SUM-0"
  )
)
#> Accomodation statistics as of 1974 according to seasons
#> 
#> Database: detouextregsai (STATcube)
#> Measures: Arrivals, Nights spent
#> Fields: Season/Tourism Month <47>, Country of origin <4>
#> 
#> Request: [2022-08-29 09:52:13]
#> STATcubeR: 0.5.0.1

schema <- sc_schema_db("detouextregsai")
region <- schema$`Other Classifications`$`Tourism commune [ABO]`$
  `Regionale Gliederung (Ebene +1)`
month <- schema$`Mandatory fields`$`Season/Tourism Month`

x <- sc_table_custom(
  schema,
  schema$Facts$Arrivals,
  list(month, region),
  recodes = c(
    sc_recode(region, total = FALSE, map = list(
      region$Achensee,
      list(region$Arlberg, region$`Ausseerland-Salzkammergut`)
    )),
    sc_recode(month, total = FALSE)
  )
)
x$tabulate()
#> # A STATcubeR tibble: 92 x 3
#>    `Season/Tourism Month` `Tourism commune [ABO]`           Arrivals
#>    <date>                 <fct>                                <dbl>
#>  1 2000-01-01             Achensee                             90947
#>  2 2000-01-01             Arlberg;Ausseerland-Salzkammergut   209356
#>  3 2000-06-01             Achensee                            133523
#>  4 2000-06-01             Arlberg;Ausseerland-Salzkammergut   109769
#>  5 2001-01-01             Achensee                             93178
#>  6 2001-01-01             Arlberg;Ausseerland-Salzkammergut   199256
#>  7 2001-06-01             Achensee                            137138
#>  8 2001-06-01             Arlberg;Ausseerland-Salzkammergut   112685
#>  9 2002-01-01             Achensee                            100464
#> 10 2002-01-01             Arlberg;Ausseerland-Salzkammergut   212284
#> # … with 82 more rows