〔経理・会計監査〕仕訳データをQlikViewで仕訳型別に集計すれば分析・集計も簡単！②

会社によっては、SAP以外の会計システムを利用しており、仕訳データを出力すると、以下のように借方と貸方とが並んだ状態で出力されることも多いと思います。

年度	伝票番号	借方科目	借方金額	貸方科目	貸方金額
2021	899546	1111	2,350	3111	2,000
2021	899546			3112	350

今回はこんなデータをQlikViewで簡単に分析・集計する方法をご紹介します！

なお、以前SAP仕訳データをQlikViewで分析・集計する方法をご紹介しましたが、大部分はほとんど同じです！

ただ、今回の仕訳データは借方・貸方とデータ列が分かれているので、そこを集計し、集約することが必要になります。

できること
簡単なデータでやってみる

できること

借方・貸方と分かれたデータを集約することができる
仕訳ごとのPLインパクトを一瞬で集計できる
仕訳ごとの勘定科目の組合せ（仕訳の型）を一瞬で集計できる

簡単なデータでやってみる

仕訳データを用意

以下のようなデータを用意し、「仕訳データ.xlsx」として保存します。
保存先はQlikViewのqvwファイルの保存先と同一のフォルダとします。

年度	伝票番号	借方科目	借方金額	貸方科目	貸方金額
2020	393639	2111	1,000	1111	1,000
2020	427981	4111	3,000	2111	3,000
2020	892457	1111	5,000	3111	5,000
2021	393639	2111	1,000	1111	1,000
2021	427981	4111	3,000	2111	3,000
2021	892457	1111	5,000	3111	5,000
2021	899546	1111	2,350	3111	2,000
2021	899546			3112	350

科目マスタを用意

以下のようなデータを用意し、「科目マスタ.xlsx」として保存します。
保存先はQlikViewのqvwファイルの保存先と同一のフォルダとします。

※PLインパクト係数は仕訳データ貸借金額×PLインパクト係数＝PLインパクトとなるように手入力したものです。PL科目には-1、BS科目には0として分類します。

科目	科目名	PLインパクト係数
1111	売掛金	0
2111	買掛金	0
3111	売上	-1
4111	仕入	-1
3112	売上2	-1

ロードスクリプトを作成

ロードスクリプトの参考例です。
できるだけ平易なスクリプトにしています。

//科目マスタを作成して読込
科目マスタ:
LOAD  *
FROM
科目マスタ.xlsx
(ooxml,  embedded  labels);

//科目マスタより、科目→科目名変換用のマッピングを生成
map_科目名:
Mapping    LOAD    科目,    科目名
Resident    科目マスタ;

//科目マスタより、科目→PLインパクト係数変換用のマッピングを生成
map_PLインパクト係数:
Mapping    LOAD    科目,    PLインパクト係数
Resident    科目マスタ;

//マッピング生成完了後に不要となった科目マスタを削除
DROP    Table    科目マスタ;

//仕訳データを仕訳データ1として読込
//仕訳データ1は何も加工されていないデータ
仕訳データ1:
LOAD  *
FROM
仕訳データ.xlsx
(ooxml,  embedded  labels);

//仕訳データ1から借方科目・借方金額に関連するデータを読込
仕訳データ2:
LOAD  *,  
          借方科目  as  科目,  
          借方金額  as  貸借金額
Resident  仕訳データ1
Where  not  IsNull(借方科目);

//仕訳データ1から貸方科目・貸方金額に関連するデータを読込み、追加（join）
join
LOAD  *,  
          貸方科目  as  科目,  
          -貸方金額  as  貸借金額
Resident  仕訳データ1
Where  not  IsNull(貸方科目);

//不要になったデータは即削除
DROP  Table  仕訳データ1;

//各マッピングと紐づけ、科目名・PLインパクトを計算
仕訳データ:
LOAD    *,    
                    ApplyMap('map_科目名',科目,'不明科目')    as    科目名,
                    ApplyMap('map_PLインパクト係数',科目,'不明科目')    *    貸借金額    as    PLインパクト
Resident  仕訳データ2;

//不要になったデータは即削除
DROP  Table  仕訳データ2;

//年度・伝票番号をキーとして仕訳データを集計
//年度と伝票番号を組み合わせれば一意の伝票を指定することができるのが通常なので、両者をキーとして集計します。
仕訳型サマリ:
LOAD    年度,
	    伝票番号,
	    concat(DISTINCT    科目名,    '    |    ',    科目)  as  仕訳型,
	    sum(PLインパクト)  as  PLインパクト（伝票ごと）
Resident    仕訳データ
Group    by    年度,    伝票番号;

//科目マスタを作成して読込

科目マスタ:

LOAD *

FROM

科目マスタ.xlsx

(ooxml, embedded labels);

//科目マスタより、科目→科目名変換用のマッピングを生成

map_科目名:

Mapping LOAD 科目, 科目名

Resident 科目マスタ;

//科目マスタより、科目→PLインパクト係数変換用のマッピングを生成

map_PLインパクト係数:

Mapping LOAD 科目, PLインパクト係数

Resident 科目マスタ;

//マッピング生成完了後に不要となった科目マスタを削除

DROP Table 科目マスタ;

//仕訳データを仕訳データ1として読込

//仕訳データ1は何も加工されていないデータ

仕訳データ1:

LOAD *

FROM

仕訳データ.xlsx

(ooxml, embedded labels);

//仕訳データ1から借方科目・借方金額に関連するデータを読込

仕訳データ2:

LOAD *,

借方科目 as 科目,

借方金額 as 貸借金額

Resident 仕訳データ1

Where not IsNull(借方科目);

//仕訳データ1から貸方科目・貸方金額に関連するデータを読込み、追加（join）

join

LOAD *,

貸方科目 as 科目,

-貸方金額 as 貸借金額

Resident 仕訳データ1

Where not IsNull(貸方科目);

//不要になったデータは即削除

DROP Table 仕訳データ1;

//各マッピングと紐づけ、科目名・PLインパクトを計算

仕訳データ:

LOAD *,

ApplyMap('map_科目名',科目,'不明科目') as 科目名,

ApplyMap('map_PLインパクト係数',科目,'不明科目') * 貸借金額 as PLインパクト

Resident 仕訳データ2;

//不要になったデータは即削除

DROP Table 仕訳データ2;

//年度・伝票番号をキーとして仕訳データを集計

//年度と伝票番号を組み合わせれば一意の伝票を指定することができるのが通常なので、両者をキーとして集計します。

仕訳型サマリ:

LOAD 年度,

伝票番号,

concat(DISTINCT 科目名, ' | ', 科目) as 仕訳型,

sum(PLインパクト) as PLインパクト（伝票ごと）

Resident 仕訳データ

Group by 年度, 伝票番号;

リロード

リロードで読み込めば、完了です！
あとは、見たいデータをテーブルに表示したり、集計したい金額を集計したり、フィルターをかけたり、様々な分析が軽々できます！

たとえば、こんな感じにテーブルやチャート（pivot table）を出して分析するとよいと思います！