Creating a magic table library

To begin creating a magic table, you first need to create a library containing blocks that describe the underlying logic of the magic table.

The following library contains blocks with underlying query logic for different scenarios in the BI tools. The blocks perform logic related to creating a cross-tabulation of hourly average weather data. Depending on the SQL query, the magic table will fetch a different underlying 1010data table. The user can select a particular year, and the block will fetch the underlying table for that year and create a cross-tabulation for that year's data. The BI tools user can also filter the data by state, time zone, desired metric (such as temperature), and weather station location. In addition, the BI tools user can choose a temperature unit of Fahrenheit or Celsius, and the underlying query logic converts the data on the fly. In this way, the BI tools end user can have easy access to a wide variety of scenarios for creating dashboards. The underlying calculations happen behind the scenes within the Macro Language code.

Note: 1010data recommends that the underlying blocks for magic tables live in a global namespace so that they are easier to access from anywhere.

<library> 
 <resource name="mtdemo">
   
   <defblock name="valid" param="">
    <switch on_="{@param}">
      <case when_="year">
        <directory folder="pub.demo.weather"/>
          <sel value="beginswith(path;'pub.demo.weather.hourly')"/>
          <willbe name="year" value="strdrop(path;'';23)"/>
          <colord cols="year"/>
      </case>
      <case when_="station">
        <base table="pub.demo.weather.stations"/>
        <willbe name="station" label="station" value="splice(name state;', ')"/>
        <tabu breaks="station">
          <break col="station" sort="up"/>
        </tabu>
      </case>
      <case when_="state">
        <base table="pub.demo.weather.stations"/>
        <tabu breaks="state">
          <break col="state" sort="up" relabel="state"/>
        </tabu>
      </case>
      <case when_="tz">
        <base table="pub.demo.weather.stations"/>
        <tabu breaks="tz">
          <break col="tz" sort="up" relabel="tz"/>
        </tabu>
      </case>
      
      <case when_="unit">
        <table>celsius;fahrenheit</table>
      </case>
      
    </switch>
   </defblock>
     
   <defblock name="validlist" paramlist="{}">
     <base table="default.empty"/>
     <for param="{@paramlist}" tally="@i">
       <if test="{@i=0}">
         <then>
           <call block="valid" param="{@param}"/>
           <willbe name="c1" value="0"/>
         </then>
         <else>
           <merge table2="default.empty" match="pad">
             <call block="valid" param="{@param}"/>
             <willbe name="c1" value="0"/>
           </merge>
         </else>
       </if>
     </for>
     <colord hide="c1"/>
   </defblock>
   
   <defblock name="withweatherfor" year="">
     <if test="{@year=''}">
       <then>
         <base table="pub.demo.weather.hourly90"/>
         <merge table2="pub.demo.weather.hourly91"/>
         <merge table2="pub.demo.weather.hourly92"/>
         <merge table2="pub.demo.weather.hourly93"/>
         <merge table2="pub.demo.weather.hourly94"/>
         <merge table2="pub.demo.weather.hourly95"/>
       </then>
       <else>
         <let year="{if(@year>2000;@year-2000;@year>1900;@year-1900;@year)}">
           <base table="pub.demo.weather.hourly{@year}"/>
         </let>
       </else>
     </if>
   </defblock>
   
   <defblock name="selweatherregion" tz="" state="" station="">
     <link table2="pub.demo.weather.stations" col="id" type="select">
       <if test="{@station<>''}">
         <sel value="splice(name state;', ')={qv(@station)}"/>
       </if>
       <if test="{@state<>''}">
        <sel value="state={qv(@state)}"/>
       </if>
      <if test="{@tz<>''}">
        <sel value="tz={qv(@tz)}"/>
      </if> 
     </link>
   </defblock>
   
   <defblock name="date_hour_cross" metric="temp" unit="celsius" measure="avg"> 
     <if test="{@unit='fahrenheit'}">
       <then>
         <amend name="{@metric}" value="32+(9/5)*clip({@metric};-50;50)"/>
       </then>
       <else>
         <amend name="{@metric}" value="clip({@metric};-50;50)"/>
       </else>
     </if> 
     <tabu breaks="date" cbreaks="hour" clabels="short">
       <tcol source="{@metric}" fun="{@measure}" label="{@metric}"/>
       
     </tabu>
   </defblock>
   
   <defblock name="mt_date_hour_cross" selects="{:}" wheres="{:}">
     <if test="{@selects._members._length>0}">
       <then>
         <call block="validlist" paramlist="{@selects._members}"/>
       </then>
       <else>
         <let year="" station="" state="" tz="" metric="temp" unit="celsius" measure="avg">
           <for key="{@wheres._members}">
             <setv name="{@key}" value="{@wheres[@key]}"/>
           </for>
           
           <call block="withweatherfor" year="{@year}"/>
           <call block="selweatherregion" tz="{@tz}" state="{@state}" 
              station="{@station}"/>
           <call block="date_hour_cross" metric="{@metric}" unit="{@unit}" 
              measure="{@measure}"/> 
           <willbe name="year" value="int({qv(@year)})"/>
           <willbe name="station" value="{qv(@$station)}"/>
           <willbe name="state" value="{qv(@$state)}"/>
           <willbe name="tz" value="int({qv(@tz)})"/>
           <willbe name="metric" value="{qv(@$metric)}"/>
           <willbe name="unit" value="{qv(@$unit)}"/>
           <willbe name="measure" value="{qv(@$measure)}"/> 
         </let>
       </else>
     </if>
   </defblock>
   
   
  </resource>
</library>