NFCタグで、家の照明を消す(Lambda経由)

先日、AWS IoTボタンでRemoを動かすという記事を書きました。

AWS IoTボタンで電気を消す

しかし、このIoTボタンはすでに販売中止。。。代替品も値段が高くて手が出しづらいので、NFCタグで動かすようにしました。

NFCタグとは?

超ざっくりいうと、スマホでデータがやり取りできる、すごく容量の小さいメモリーカードみたいなものです。NFCタグにURLを書き込んでおいて、スマホでタッチしたらそのURLを読み取ってそこに行く、みたいな使い方です。

名前などのテキスト情報なんかも書き込めるので、名刺代わりに使えたりもするそうです。

ちなみに、おサイフケータイが入っているスマホだったら大体対応しているようです。iPhoneだとSEの第二世代くらいから使えるみたい(嫁のiPhoneSEで実証済み)。

NFCタグを使えばタスクの自動化などいろいろなことができます。https://goworkship.com/magazine/smartphone-nfc-tag/

もっと詳しく知りたい方は、「NFCタグ」でググってみてください。

どのNFCタグを買うか?

NFCタグには、Ntag213とか215とかの種類があります。容量が違うだけのようです。
https://www.asiarfid.com/ja/difference-ntag213-ntag215-ntag216.html

今回紹介するやり方は、NFCタグにURLを書き込むだけなので、215で十分です。(213だとちょっと不安)。

ちなみに、タスク実行機能を使って、直接RemoのAPIを叩くやり方だと、215でも足りませんでした。もうちょっと容量の大きいNtag216を買いましょう。Switchbotが出しているNFCタグは、こいつです。NFCタグはSwitchbot製でも、ちゃんとRemoも動きますよ。ただ3枚で1,000円弱と、ちょっとお高め。

今回の仕組み

上の記事にあるようなタスク実行機能を使って、RemoのAPIを直接実行させることもできます。このやり方であれば、Lambdaを使う必要もありません。

がしかし!それだと自分のスマホだけでしか動かせないんです。。。今回したいことは家の電気を消すこと、すなわち嫁のiPhoneでも動いてくれないといけない。でも、タスク実行機能だと、嫁のスマホでは動かない。。。(実際試してダメでした)

ということで、以下のようなやり方にしました。

  NFCタグに、電気を消すためのLambdaを動かすURLを書き込む
  ↓
  スマホでNFCタグにタッチしたら、そのURLを読み込んで、そこへ行く
  ↓
  Lambdaが動いて、「電気消せ」とRemoに指示を出す
  ↓
  Remoが電気を消す

Lambdaを動かすURLを作る

いわゆるAPI化ってやつです。電気を消すためのLambda関数をAPI化し、特定のURLを開いたらこの関数が動き出すようにします。

電気を消すためのLambda関数を作る

AWS IoTボタンで電気を消すときに作ったものがそのまま流用できます。
以下の記事を参考にしてください。

AWS IoTボタンで電気を消す

一番最後に書いてある「おまけ:実際に使っているコード」を使ってみてください。

Lambdaを動かすURLを作る

画面の上のほうに、「関数の概要」というところがあり、その近くに「トリガーを追加」というボタンがあるので、それをクリック。

トリガーの種類で「API Gateway」を選ぶ。

その他の設定は以下の通り。入力したら、右下の「追加」ボタンをクリック。

すると、画面下に「トリガー」欄がでてきているはずです。赤で囲った部分が、Lambda関数を動かすためのURLです。そのURLへ行くだけで、この関数が動き出します。

このURLをこぴって、ブラウザのURL欄に貼り付けて、実際にそのURLに行ってみてください。それで電気が消えれば、成功です。

ちなみに、このURLに行ければ、だれでも動かせます。セキュリティもへったくれもありません♪

URLをNFCタグに書き込む

スマホアプリを使います。僕はNFC Toolsというアプリを使いました。「書く」タブから「レコードを追加」を選択し、「URL」→上記のURLをペースト。

あとは「書く」ボタンを押し、NFCタグにかざすだけでOKです。

ちなみに、NFCタグは何回でも書き換えられるので、安心して書き込みテストしてもらってOKです。

これで設定完了!

NFCタグで電気を消す

スマホの画面ロックを解除し、NFCタグにスマホをかざしてください。画面ロック状態だと動かないのでご注意を。

NFCタグにタッチしたサイン音がピロッとなり、数秒後にRemoが動き出したら成功です。

AWS IoTボタンで電気を消す

リビング照明にはリモコンがついていて、Nature Remoでつけたり消したりしているんですが(実際はアレクサがやってくれている)、寝るときは壁スイッチで消すことが多いです。そっちのほうが動線的に自然だし。

ただ、これだと主電源が切られたようなもんで、もう一回その壁スイッチを押して主電源をOnにしないとリビング照明がつきません。「アレクサ、電気つけて」といってNature Remoがピッて言っても、電気がつきません。

ということで、壁スイッチの横にAWSボタンを置いて、壁スイッチを押さずにAWSボタンを押すことで電気を消すようにしました。以下の図の、右下のボタンです。(左上のSwitchbotのボタンは今回は関係ないです)

ちなみに、これ、正式名称は「AWS IoT Enterprise Button」というみたいですが、2022/5月時点で販売中止になってました。安くてよかったのに。。代替品はめちゃ高いので買う気がしません。。。

https://aws.amazon.com/jp/iot-1-click/devices/

なので、ボタンはこの1個だけで、今後の増設はNFCタグで行います。詳しくは以下で!

仕組み = ボタン → Lambda → Nature Remo → 電気消す

ボタンを押したら、Lambdaが動いてNatureRemoに指示を出し、NatureRemoがその指示に従って電気を消す という流れです。

以前はIFTTTを使っていましたが、IFTTTが有料になったため、IFTTTを使わずにLambdaだけで行います。

Lambdaとは、AWSが提供しているサービスの一つで、自分でPythonなどのプログラムを書いて、SwitchbotやNature Remoなどを動かすことができます。ちなみに、AWSのサービスは有料で、クレカの登録が必要になります。が、今回のような「単にボタンを押したらRemoを動かす」だけだったら、月に100円程度です♪

事前準備

AWS IoTボタンのセットアップ

スマホを使って登録します。AWS IoT 1-Clickというアプリをスマホにインストールしてください。登録までの流れはアプリがわかりやすくリードしてくれます。

ちなみに登録にはDSN番号なるものが必要です。ボタンの背面に記載されているので、ボタンを壁に貼り付ける前にメモっておく必要があります!
(箱にも書かれているので、箱があれば大丈夫ですが)

AWSアカウントの作成

まずはAWSアカウントを作りましょう。利用料金がかかるしクレジットカードの登録も必要ですが、臆さず登録しましょう!

https://aws.amazon.com/jp/register-flow/

すさまじくいろいろなサービスがありますが、このなかの「Lambda(ラムダ)」を使います。

Nature RemoのTOKENを取得

以下のサイトへ行って、TOKENを発行しましょう。画面に一度そのTOKENが出てくるだけで、あとから再表示はできないようなので、しっかりTOKENをメモっておきましょう。

https://home.nature.global/

僕は以下のサイトを参考にしました。
https://blog-and-destroy.com/12297

これで準備は完了です!次はいよいよLambdaでプログラムを書いて、自分のNature Remoとやり取りします。

Nature Remoに登録されている家電IDを調べる

Lambda関数の新規作成(空)

AWSで作業をします。AWSコンソールに行ったら、まずはリージョンのチェック!画面の右上でリージョンを変更できます。「東京」になっていればOK。「バージニア北部」とかになっていれば、「東京」に変更してください。これ、すごく重要です!

次に、画面上部にサービスの検索窓があるので、そこに「Lambda」と入れて、出てきたサービス欄からLambdaをクリック。

右上の「関数の作成」をクリック。
  関数名 = 「NatureRemo_GetDevieID」
  ランタイム = 「Python3.9」
(2022/5時点。その時の最新のを選べばOKです)
と入れて、あとはデフォルト設定のままで、右下の「関数の作成」ボタンをクリック。

以下のブログが参考になりました。
https://zenn.dev/nakam_aws/articles/46fcc003855aef

環境変数の設定(TOKEN)

環境変数へ、TOKEN情報を書き込みます。(プログラム内に直書きでもOKですが、環境変数に書いたほうがすっきりします)

「設定」タブ→「環境変数」→「編集」と押し、

「環境変数の追加」で、キー欄に「TOKEN」と、値欄に先ほどメモしたRemoのTOKEN番号を貼り付けましょう。
  キー 「TOKEN」
  値  RemoのTOKEN番号

入力したら、保存してください。

Remoの家電ID(ApplianceID)を取得するプログラムを書く①

まずは、以下のコードを貼り付けてください。ちなみに、はまったところは、「認証が通らない」「Requestsライブラリが使えない」といった点でした。

import json
import os
import urllib.request

def lambda_handler(event, context):

    #--- TOKEN ---------------#
    TOKEN = os.environ.get('TOKEN')

    #--- ヘッダ --------------#
    headers = {
        'accept': 'application/json',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Authorization': 'Bearer ' + TOKEN,
    }

    #-- 家電ID取得用のURL --#
    url = 'https://api.nature.global/1/appliances'

    #--- 取得 
    req = urllib.request.Request(url=url,headers=headers, method='GET')

    #--- 取得 ----------------#
    res = urllib.request.urlopen(req)
    jjj = json.loads(res.read())

    #--- ログに出力(全部)---#
    print('-----------------')
    print(json.dumps( jjj, indent=4, ensure_ascii=False ))
    print('-----------------')

このコードを貼り付けたら、上のほうにある「Deploy」ボタンを押してください。これでプログラムが実行できる状態になりました。

Deployボタンを押した後、テストタブを開いて

テストタブ

右側にあるオレンジの「テスト」ボタンを押してください。

以下のような画面がでて、テストが成功したら、もう一回「コード」タブに戻ってください。

成功したら、このような緑の枠組みが出てきます。

「Execution Result」が表示されていると思います。そこにログが出力されています。

ただ、ログがぐちゃっとなって見づらいと思います。

Remoの家電ID(ApplianceID)を取得するプログラムを書く②

ID/名前/ボタン情報 だけを出力するようにします。上記のコードの最後に以下のプログラムを追加してください。

    #--- ログに出力(IDだけ)---#
    for j in jjj:
        print('-----------------')
        print('id=' + j['id'])
        print('name=' + j['nickname'])
        print('signal=' + json.dumps(j['signals'], ensure_ascii=False))

Deployを忘れずに!Deployしたら、テストタブに行って、テスト実行して、コードに戻って、ログを見てみてください。

以下のようなのがログに出てきていると思います。idが「家電ID」で、ついでに後で必要になるシグナルIDも取得できました。

Remoに登録されているリビング照明の家電IDは「xxxxx」で、照明をつけるオンボタンのシグナルIDは「yyyyy」、オフボタンのシグナルIDは「zzzzz」という感じです。

-----------------
id=xxxxx
name=電気
signal=[{"id": "yyyyy", "name": "オン", "image": "ico_on"}, {"id": "zzzzz", "name": "オフ", "image": "ico_off"}]
-----------------

ちなみに、シグナルIDに何も出ていないことがあります。これは、リモコンのボタン1つひとつを登録するのではなく、リモコンをマルっと登録したものになります。TVとかエアコンとかに多く起こります。この場合、ちょっとやり方が変わります(後述)。

※ シグナルに何も出てこないパターン
-----------------
id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
name=テレビ
signal=[]
-----------------

LambdaでRemoを動かす!

シグナルIDがあるケース

うちのRemoに登録されている家電「電気」は、シグナルIDがあったので、このケースです。

新たにLambda関数を新規作成しましょう。名前は、「NatureRemo_LightOn」とか。
そして、環境変数にTOKEN情報を書き込んでください。

そしたら、デフォルトで入っているコードを消して、以下のコードを貼り付けてください。
また、「yyyyy」の部分(url_lightのところ)を、上記で取得したシグナルIDに書き換えてください。

import json
import os
import urllib.request

def lambda_handler(event, context):

    #--- TOKEN ---------------#
    TOKEN = os.environ.get('TOKEN')

    #--- ヘッダ --------------#
    headers = {
        'accept': 'application/json',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Authorization': 'Bearer ' + TOKEN,
    }

    #--- 電気用のURL    ------#
    url_light = 'https://api.nature.global/1/signals/yyyyy/send' #←要修正!yyyyy

    #--- 電気を消す -------------------------#
    req = urllib.request.Request(url=url_light, headers=headers, method='POST')
    urllib.request.urlopen(req)

そうです。家電IDは使いません♪ シグナルIDだけで動きます!

Deployし、テストタブに行き、テストを実行してください。これで電気がつけば、成功です!

僕は、初めて成功したときは結構感動しました^^

シグナルIDがないケース

TVやエアコンなど、リモコンをマルっと登録した家電にはシグナルIDがつきません。そんな時は、以下のような感じで動かします。

関数を新規作成し、環境変数にTOKEN情報を入れ、コード欄に以下のコードを貼り付けてください。

こちらのケースでは、家電IDを使うことになります。
urlのところにあるxxxxx・・・を、家電IDに書き換えてください。

import json
import os
import urllib.request


def lambda_handler(event, context):

    TOKEN = os.environ.get('TOKEN')
    url = 'https://api.nature.global/1/appliances/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/tv'

    headers = {
        'accept': 'application/json',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Authorization': 'Bearer ' + TOKEN,
    }

    data = b'button=power'

    req = urllib.request.Request(url=url, data=data, headers=headers, method='POST')
    urllib.request.urlopen(req)

ポイントは、「data = b’button=power’」のところ。”power”が、電源ボタンを指します。すなわち、これを動かすと、TVリモコンの電源ボタンを押した動きになります。

このように、どのボタンを押すかをデータで渡す必要があるのですが、その渡し方にはまった。。。
以下のブログに解決策が書かれており、非常に助かりました。
https://qiita.com/souring001/items/68b5397c1750c325f61a

TVボタン名の調べ方

ボタン名(name)を調べるには、以下のコードを新規作成して回してみてください(TOKEN情報を環境変数に入れるのを忘れずに)。

import json
import os
import urllib.request

def lambda_handler(event, context):

    #--- TOKEN ---------------#
    TOKEN = os.environ.get('TOKEN')

    #--- ヘッダ --------------#
    headers = {
        'accept': 'application/json',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Authorization': 'Bearer ' + TOKEN,
    }

    #-- DeviceID取得用のURL --#
    url = 'https://api.nature.global/1/appliances'

    req = urllib.request.Request(url=url,headers=headers, method='GET')

    #--- 取得 ----------------#
    res = urllib.request.urlopen(req)
    jjj = json.loads(res.read())

    #--- ログに出力(IDだけ)---#
    for j in jjj:
        print('-----------------')
        print('id=' + j['id'])
        print('name=' + j['nickname'])
        print('signal=' + json.dumps(j['signals'], ensure_ascii=False))
        if('tv' in j.keys()):
            print('TV=' + json.dumps(j['tv']['buttons'], ensure_ascii=False))
        if('aircon' in j.keys()):
            print('aircon=' + json.dumps(j['aircon'], ensure_ascii=False))

ログに、以下のような情報が出ているはずです。ここから、どのボタンがどんな名前か勘を働かせて特定します。

TVのボタン名一覧

これで、「Lambda→Remo」の部分が組みあがりました!一番面倒なLambdaはこれでおしまいです。

あともう少しです!「ボタン→Lambda」の部分を組み込めば完成です!!

ボタンを押したらLambdaを動かす

こちらは、ボタンを登録したときに使ったAWS IoT 1-Clickスマホアプリを使います。

「プロジェクト」タブから新規作成し、「デバイステンプレート」で、先ほど作ったLambda関数(NatureRemo_LightOn)を選択し、「プレイスメント」で冒頭に登録したIoTボタンを指定してあげるといった流れです。

僕はスマホアプリでトラブルなく作成できましたが、他の人はWebのAWSサービス(IoT 1-Click)で設定されてる人が多いみたいです。
https://qiita.com/hayao_k/items/d36d4b2579d0bbbe21a9
などがとても分かりやすかったです。

これで完成!ボタンをぽちっとして、ちゃんと電気が消えるか試してみましょう♪

おまけ:実際に使っているコード

実際には、電気を消すと同時にTVも消したいですよね。また、このAWS IoTボタンは、ダブルクリックなどにも対応してます。なので、ダブルクリックしたときは、電気をつけるようにしています。

テレビのボタンって、「On/Offボタン」が独立してそれぞれあるわけじゃなく、「電源ボタン」がひとつしかない。。でも、「電源がついてなければ電源ボタン押すのスルーして」なんてことはできない。なので、ちょっとした工夫が必要です。

いったん「入力切替」ボタンを押しています。もしテレビが消えていたら、これでいったんテレビがOnになります。もしテレビがついていたら、ついたままです。その後、電源ボタンを押しています。電源ボタンを押す前に「必ずついてる状態」にしている感じです。

加えて、NFCタグからも実行させているので、その部分も書き加えています。

なお、環境設定に、TOKEN / 照明OnのボタンシグナルID / 照明OffボタンのシグナルIDの3つを登録しています。

import json
import os
import urllib.request
import time

def lambda_handler(event, context):

    #--- ID情報を環境設定から取得 ---#
    ID_TV = os.environ.get('ID_TV')
    ID_LIGHTOFF = os.environ.get('ID_LIGHTOFF')
    ID_LIGHTON  = os.environ.get('ID_LIGHTON')

    #--- TOKEN ---------------#
    TOKEN = os.environ.get('TOKEN')

    #--- ヘッダ --------------#
    headers = {
        'accept': 'application/json',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Authorization': 'Bearer ' + TOKEN,
    }

    #--- AWSボタンとNFCタグの両方からくる-> NFCタグから来たら、シングルクリックにする
    if(json.dumps(event).find('deviceEvent') == -1):
        clicktype = 'SINGLE'
    else:
        clicktype = event['deviceEvent']['buttonClicked']['clickType']
        

    #----------------------------------------------------------------#
    #--- ダブルクリックなら電気をつけて、それ以外は電気を消す -------#
    #----------------------------------------------------------------#

    #--- 電気用のURL    ------#
    if (clicktype == "SINGLE"):
        url_light = 'https://api.nature.global/1/signals/' + ID_LIGHTOFF + '/send'
    elif (clicktype == "DOUBLE"):
        url_light = 'https://api.nature.global/1/signals/' + ID_LIGHTON  + '/send'
    elif (clicktype == "LONG"):
        #ボタン長押し
        url_light = 'https://api.nature.global/1/signals/' + ID_LIGHTOFF + '/send'
    else:
        url_light = 'https://api.nature.global/1/signals/' + ID_LIGHTOFF + '/send'

    #--- 電気を消す or つける -------------------------#
    req = urllib.request.Request(url=url_light, headers=headers, method='POST')
    urllib.request.urlopen(req)




    #--- TV用のURL  ---------#
    url_TV = 'https://api.nature.global/1/appliances/' + ID_TV + '/tv'

    #----------------------------------------------------#
    #--- ダブルクリックじゃなければ、TVを消す   ---------#
    #----------------------------------------------------#
    #いったんつける
    if (clicktype == "SINGLE"):
        data = b'button=select-input-src' #電源ボタンじゃないのがミソ
        req = urllib.request.Request(url=url_TV, data=data, headers=headers, method='POST')
        urllib.request.urlopen(req)

        time.sleep(5) #ちょっと待つ

        #--- 消す
        data = b'button=power'
        req = urllib.request.Request(url=url_TV, data=data, headers=headers, method='POST')
        urllib.request.urlopen(req)

以上、参考になれば幸いです。ありがとうございました。

アレクサTips : 電話番号を調べる

コロナ禍でお店の営業時間が短縮になっていますが、豊洲のコーナンの営業時間が知りたくって、電話番号を調べようと。

ふとアレクサに話しかけたら、ちゃんと電話番号を教えてくれました。

「アレクサ、豊洲にあるコーナンの電話番号教えて」・・・〇
「アレクサ、豊洲のコーナンの電話番後う教えて」・・・×

場所を指定するときは、「〇〇にある」と言わなきゃダメ見たい。

けっこうベタな内容ですが、こうやって使うことがなかった。。。

Qlik便利関数 : データの一時保存

「巨大なデータを扱っていて、途中でコケた場合、また最初から回さなければならない。途中段階で一時的にデータを保存したいなぁ」というときに役立つ便利関数です。

使い方

/*---------------*/
重たい処理
いろいろなデータ処理
いろいろなデータ処理

call Benri.MyStore('テーブル名');
/*---------------*/
call Benri.MyLoad('テーブル名');

といった感じで使います。

まずそのまま回すと、[c:\temp]にQVDとしてデータが保存されます。2回目以降は、1つ目の「/*————-*/」の右端を消してあげれば、MyLoadの直前までコメントアウトされ、重たい処理をすっ飛ばして一時保管されたデータが読み込まれます。

サンプルプログラム

最初はそのまま回します。すると、testdataがc:\temp\QVW名_testdata.qvdとして保存されます。

2回目以降は、「★」部分の右端「/」を削除して回します。すると、MyLoadまでの処理がすっ飛ばされて、MyLoadによってtestdataが読み込まれます。

ただし、すっ飛ばしたい部分までは別のコメントアウト「/*—-*/」は使えなくなります。すっ飛ばしたい部分内にコメントを残す場合は、「//」を使ってください。

//便利関数の読み込み
$(Include=C:\temp\Benri.txt);

/*------------*/ //←★次に回すときは、右端の「/」を消して回す
//テストデータ
testdata:
	load		*
	Inline [
		prod, ym  , a1, a2
		魚, 201901, 10, 10
		魚, 201902, 20, 
		魚, 201903,   , 30
	]
;

//使用例
call Benri.MyStore('testdata');
/*-----------*/
call Benri.MyLoad ('testdata');

仕様

以下のような内容です。超絶シンプル♪


sub Benri.MyStore(__tbl)
	let __aaa =  Left(DocumentName(), len(DocumentName())-4) ;
	store $(__tbl) into [c:\temp\$(__aaa)_$(__tbl).qvd](qvd) ;
	drop Table $(__tbl) ;
	set __aaa=;
end sub

sub Benri.MyLoad (__tbl)
	let __aaa =  Left(DocumentName(), len(DocumentName())-4) ;
	$(__tbl): NoConcatenate
		load * from [c:\temp\$(__aaa)_$(__tbl).qvd](qvd)
	;
	set __aaa=;
end sub

Qlik便利関数:欠損値をゼロ埋め

欠損値に0を入れてくれる便利関数です。複数の変数がある場合は、スペースで区切ります。区切るスペースは1個でも複数個でも大丈夫です。

call Benri.NulltoZero('テーブル名', '変数1 変数2  変数3');

といった感じで使います。変数1/変数2/変数3それぞれの欠損値を0に変換します。

使い方

以下の「benri.txt」をダウンロードして、適当なところに保存してください。

サンプルプログラム

ダウンロードしたbenri.txtを c:\temp に保存して実行してみてください。

//便利関数の読み込み
$(Include=C:\temp\Benri.txt);

//テストデータ
testdata:
	load		prod, ym
			  , if(a1='', Null(), a1) as amt1
			  , if(a2='', Null(), a2) as amt2
	Inline [
		prod, ym  , a1, a2
		魚, 201901, 10, 10
		魚, 201902, 20, 
		魚, 201903,   , 30
		魚, 201904, 40, 
		魚, 201905, 50, 50
		魚, 201906, 60, 60
	]
;

//使用例
call Benri.NulltoZero('testdata', '   amt1   amt2   ');

仕様

以下のようなサブルーチン内容です。
構造はいたってシンプルですが、引数(__vars)にスペースが複数個あった場合の処理で結構手間取ってます。


sub Benri.NulltoZero(__tbl, __vars)

	//左右の無駄なスペース除去
	let __vars = Trim('$(__vars)');
	
	//スペースを1つにする
	let __x = Index('$(__vars)', '  ') ;
	do while __x > 0
		let __x = Index('$(__vars)', '  ') ;
		let __vars = Replace('$(__vars)', '  ', ' ');
	loop
	set __x=;
	
	//変数の個数をスペースの個数を基に数える
	let __nVar = len('$(__vars)') - len(Replace('$(__vars)', ' ', '')) + 1 ;

	//処理用の各種文章
	let __s1 = '';
	let __s2 = '';
	let __s3 = '';
	let __s4 = '';
	
	for __i=1 to $(__nVar)
		let __v = SubField('$(__vars)', ' ', $(__i));
		let __s1 = '$(__s1), if(IsNull($(__v)), 0, $(__v)) as __$(__v)'	; //欠損値をゼロに埋めるための文章
		let __s2 = '$(__s2), $(__v)' 									; //ドロップ用
		let __s3 = '$(__s3), __$(__v) as $(__v)'						; //一時名から元の名前にリネーム用の文章
		let __s4 = '$(__s4), __$(__v)' 									; //ドロップ用
	next __i
	set __v=;
	
	//最初の不要なカンマを取る
	for __i=1 to 4
		let __s$(__i) = mid('$(__s$(__i))', 2) ;
	next __i
	set __i=; set __nVar=;
		
	//欠損ゼロ埋め
	__temp: NoConcatenate
		load		*
				  , $(__s1)
		Resident	$(__tbl)
	;
	drop table $(__tbl);
	//元の変数削除
	drop Fields $(__s2) from __temp ;
	//リネームして変数名を戻す
	$(__tbl): NoConcatenate
		load		*
				  , $(__s3)
		Resident	__temp
	;
	drop table __temp ;
	drop Fields $(__s4) from $(__tbl) ;
	
	set __s1=; set __s2=; set __s3=; set __s4=;
end sub

Qlik : 期間累計・直近●ヶ月

最近コロナで「直近1週間の平均感染者数」とかいう単語をよく見るようになりました。

この「直近●●の合計」など、期間累計のやり方についてです。

チャートを使えば、RangeSum/Aboveで期間累計できますが、ロードスクリプトだとそのやり方を知りません。

なので、データハンドリングで対処してます。

私は2つのやり方でやっているのですが、1つはすべての組み合わせを作ってやる方法(デカルト積)、もう一つは「過去からの総合計から●ヶ月前までの総合計を引く」方法です。

先に、テストデータを載せておきます。

//テストデータ
testdata:
	load		*
	Inline [
		prod, ym, amt
		魚, 201901, 10
		魚, 201902, 20
		魚, 201903, 30
		魚, 201904, 40
		魚, 201905, 50
		魚, 201906, 60
		魚, 201907, 70
		魚, 201908, 80
		魚, 201909, 90
		魚, 201910, 100
		魚, 201911, 110
		魚, 201912, 120
		魚, 202001, 10
		魚, 202002, 20
		魚, 202003, 30
		魚, 202004, 40
		魚, 202005, 50
		魚, 202006, 60
		魚, 202007, 70
		魚, 202008, 80
		魚, 202009, 90
		魚, 202010, 100
		魚, 202011, 110
		魚, 202012, 120	
		肉, 201901, 10
		肉, 201902, 20
		肉, 201903, 30
		肉, 201904, 40
		肉, 201905, 50
		肉, 201906, 60
		肉, 201907, 70
		肉, 201908, 80
		肉, 201909, 90
		肉, 201910, 100
		肉, 201911, 110
		肉, 201912, 120
		肉, 202001, 10
		肉, 202002, 20
		肉, 202003, 30
		肉, 202004, 40
		肉, 202005, 50
		肉, 202006, 60
		肉, 202007, 70
		肉, 202008, 80
		肉, 202009, 90
		肉, 202010, 100
		肉, 202011, 110
		肉, 202012, 120	
	]
;

すべての組み合わせを作ってやる(デカルト積)

元のデータに元のデータをどばっと総当たりでくっつけて、期間に絞って集計するやり方です。と、文章では説明が難しいので、以下のサンプルプログラムをご覧ください。過去1年間の期間累計のサンプルです。

2019/01のレコードに2019/01~2020/12のレコードをくっつける、
2019/02のレコードに2019/01~2020/12のレコードをくっつける、
・・・
2020/12のレコードに2019/01~2020/12のレコードをくっつける、
と一時的にデータが膨らみます。
その後、2020/12のレコードは「(ym2が)2020/1~2020/12までのamtをsumして、元のデータにくっつける」という処理の流れになります。

なお、このやり方だと、一時的にデータが膨大に膨れます。なので、巨大データを扱う際は不向きです。

//--- 期間累計のやり方1:デカルト積(総当たり)
temp: NoConcatenate
	load		prod, ym
	Resident	testdata
;
Outer Join
	load		prod, ym as ym2
			  , amt
	Resident	testdata
;

Left Join(testdata)
	load		prod
			  , ym 
			  , sum(amt)		as amt_直近1年
	Resident	temp
	Where		ym2 <= ym
			and ym2 >  ym-100
	Group By	prod
			  , ym
;

drop tables temp ;

過去からの総合計から●ヶ月前までの総合計を引く

こちらのほうが直感的かもしれません。

以下のサンプルプログラムでは、まず期間に関係ない(総)累計を計算し、その後1年前の累計をくっつけ、それを引いています。

データの始めから1年間経っていない部分(201901~201912)は、欠損になっています。

//--- 期間累計のやり方2:直近までの累計から1年前の累計を引く
temp:
	load		prod, ym
			  , if(Previous(prod) <> prod 
			  	  , amt
			  	  , peek(累計) + amt
			  	)				as 累計
	Resident	testdata
	Order By	prod, ym
;

//1年前の累計
left join
	load		prod 
			  , ym + 100 		as ym
			  , 累計				as 累計_1年前
			  
	Resident	temp
;

//引く
left join
	load		prod, ym
			  , 累計 - 累計_1年前	as amt_直近1年_2
	Resident	temp
;

//大元データにつけ戻し
left join(testdata)
	load		prod, ym
			  , amt_直近1年_2
	Resident	temp
;
drop tables temp ;

以上、ロードスクリプトでの累計のやり方でした。

アレクサTips 自分の携帯を探す

自分の携帯が迷子になったとき、携帯を鳴らしてくれます。

「携帯ファインダー」というスキルを使います。そのスキルの説明書き(Alexaへの話し方の例)にある文章は長ったらしいですが、「アレクサ、携帯どこ?」で反応してくれます!「アレクサ、電話どこ?」でもOK

これは便利♪

アレクサのスキルを有効にする

「携帯ファインダー」というスキルを有効にしましょう。

  • 左上の三本線→スキル・ゲーム
  • 検索窓から「携帯ファインダー」
  • 携帯ファインダーをタップ
  • 「有効にして使用する」をタップ

アレクサに話しかけて、設定を進める

私の場合、ここでトラブルに! 電話をかけても、プチッと切れる。つながらない。。。

アレクサがしゃべってるときじゃないと、電話がつながらない?みたいでした。

ということで、「03‐4540-2801」をメモっておき、携帯片手にアレクサにしゃべりかけましょう。

  • 一度電話番号「03-4540-2801」にTELしておく。(後で発信履歴からTELするため)
  • 携帯を片手に持っておく。電話をすぐにかけれるように。
  • アレクサに話しかける・・・「アレクサ、携帯どこ?」
  • アレクサがもごもご話し出す。しばらくして「スタートと言ってください」とアレクサが言うので、「スタート」と言う。
  • アレクサが電話番号を話し出す。が、この電話番号を聞いた後に電話してもつながらないので、電話番号を言い出したらすぐに発信履歴から「03-4540-2801」にTELする。
  • アレクサもしゃべりつつ、電話先でもごにょごにょ言い出す。ちょっと聞き取りづらくなる。アレクサのほうが黙るまでしばらく待つ。
  • アレクサが黙ったら、「アレクサ、携帯ファインダーのピンコードを教えて」と話しかける。
  • 4桁のピンコードを言うので、携帯にその番号を入力する。
  • 携帯のほうがごにょごにょ言って、自然にTELが切れる。これで設定完了!

接続テスト:アレクサに「携帯どこ?」「電話どこ?」

アレクサに向かって、「アレクサ、携帯どこ?」もしくは「電話どこ?」と言ってみましょう。それで自分の携帯が鳴ったらOK。

ブックカバー

まったく本を読まないわたくしですが、友人のオーダーでブックカバーを制作。

dav
dav

クロム系なので、初めてヘリ返しでつくりましたが、、、ヘリ返しってめっちゃむずい!!
最初は革包丁で地道にへりを削ってましたが全然うまくいかず。。。奥底に眠ってた「scharf fix」という手動革漉き機を使ってやると比較的うまくいきました。

https://www.scharffix-japan.com/

こいつも使い慣れるまでかなり苦戦しましたが、「あまり漉き過ぎない」!これに尽きます。0.5mmで漉いたらむらなくきれいに漉けました。そのくらいの厚みでもきれいにヘリ返せたし♪

作り方

  • 横410mm、縦185mmで革を切り出す。
  • ヘリ返しするため、上下左右12mmで漉く。
    scharf fix」を使う場合は、10mmのガイドを使えば、自然と12mmくらいになる
  • 上下左右10mmのところにラインを引く。また、左右80mmのところにラインを引く
  • 厚みを出したくなかったら、80mmのラインのところを中漉き。これもScharf Fixを使ったほうが超楽。
  • 上下左右を10mmラインでヘリ返す。角はざっくり菊寄せでOK(最終的に隠れるし♪)
  • 両サイド80mmラインで折り、ぐるっと縫って完成

有孔ボードのフックを自作 ~チューブ用~

有孔ボードのフックって、既製品はめっちゃくちゃ高くないですか?ボードは安いのに。。

ということで、有孔ボード用のフックをいろいろ自作しています。今回は、ボンドなどチューブを引っ掛けるフック(ってかホルダー)です。へらのお尻にクリップを付ければ、そいつも引っ掛けられます。

dav

作り方は、以下の動画をご覧ください。

関連記事

ラゲッジタグ

dav

先日作ったコピー用紙のバインダーでほんの少し革が余ったので、ラゲッジタグなるものを作成しました。

「これ自分のカバン!」というのがすぐにわかる目印みたいなものです。名前を入れればネームタグにも。

↑↑↑ 先日作ったコピー用紙バインダー ↑↑↑

裏地と貼り合わせて縫うだけの超単純なものだけに、コバ磨きに魂込めました♪

↓バッグにつけたところ↓

↓全体像↓

肌色っぽく映ってますが、下のと同じものです。光の加減かな?

作り方

型紙(正確な寸法は、下のほうにAIファイルを添付していますので、そちらを使ってください)
  • 型紙よりちょっと大き目で表革・裏革を切り出す。
    貼り合わせた後に切り出したほうが、コバがきれいに磨けます。
  • 全面をベタッと貼り合わせる
  • 型紙の寸法どおりに切り出す。(内側の引っ掛ける部分はまだ切らない)
  • へりを落としてひたすらコバ磨き
  • 全体をぐるっと縫う(縫い白2.5mm)
  • 内側の引っ掛ける部分を切り出す

型紙

AIファイルです。イラストレータ等で使ってください。