Skip to main content

Japan

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

文字列条件に合致する合計金額計算方法について

お世話になっております。

先日よりQlikViewを触り始めた初心者です。

下記、

ご存知の方がいらっしゃいましたら教えて頂きたくお願いいたします。

今取り組んでいるのは、

売上データと費用データを組み合わせて品番別の実際の売上金額を計算しようとしています。

売上データは、

品種,品番,売上金額となっております。

品種品番売上金額
テレビTAAA-R150万円
テレビTAAB-N100万円
テレビTBBB-C50万円
冷蔵庫RBBB-C70万円

費用データは、

種別,対象,金額という構造になっています。

種別対象費用金額
品種テレビ50万円
品番TAAA-R20万円
前方一致TAA50万円
前方一致RBBB10万円

費用データでは、

種別という項目で3種類に分かれており、

①品種全体にかかったのか、

②品番別にかかった費用なのか、

③そして品番の前方一致でかかった費用なのかを分類しています。

品番別のものは単純に集計して、

品種別のものは品種合計の売上に応じて按分しています。

困っておりますのは③の前方一致の場合で、

合致した条件に応じて対象の売上金額を算出し、品番別に按分したいと考えておりますが

やり方がわかりません。

※上記の例で行きますと下記の表になります。

品番売上金額品種別費用品番別費用前方一致費用最終売上金額
TAAA-R150万円50万*(150/300)=25万20万50万*(150/250)=30万75万
TAAB-N100万円50万*(100/300)=16.66万050万*(100/250)=20万63.34万
TBBB-C50万円50万*(50/300)=8.33万0041.67万
RBBB-C70万円0010万60万

Loadスクリプトで解決すべき問題なのか、計算式で解決すべき問題なのか

いい方法がありましたらご教授いただきたくお願いいたします。

以上、よろしくお願いいたします。

7/14 説明がわかりづらかったので追記しました。 メッセージ編集者: 健嗣 松永

7/16 前方一致の桁数が一定でなく、事例が悪かったので一部訂正いたしました。 メッセージ編集者: 健嗣 松永

Labels (2)
1 Solution

Accepted Solutions
hka
Employee
Employee

そんなに難しく考えず、match関数とlenを使えばできます。

添付の右側のストレートテーブルの右端の数式を参照してください。

これで解決するのでは?

データはDBBBを編集してDBBも前方一致のデータに追加してます。

データ量が増えた場合のパフォーマンスには注意してください。

View solution in original post

9 Replies
ykka-alto
Contributor
Contributor

一旦ロードスクリプト側で前方一致の対象と同じ粒度で、売上金額を集計します(按分売上テーブル)。
※TAAAなど左から4つ共通で取る場合であればleft関数を使用して集計します。添付ファイルではleft関数を使用しております。
集計したテーブルと前方一致費用のテーブルとを結合し、費用の項目を追加します。(按分前方一致費用項目)
上記で作成したテーブルは売上テーブルとは粒度が異なり、紐付けができないため、売上テーブルに粒度をあわせた按分品番項目を追加します。

画面側で上記で作成した項目を使用して
sum(按分前方一致費用)*sum(売上金額)/sum(按分売上金額)
と計算すると品番別に按分された金額が表示されるかと思います。

メインのコピーシートの品種別費用品番按分の数式に記載しております。

やりたいことと異なっておりましたら申し訳ありません。。。

Not applicable
Author

Kusakabe 様

ご返信頂きましてありがとうございました。

ロードスクリプト側で品番と前方一致の項目を紐付けて

按分用の売上金額集計表を作成する方法大変参考になりました。

ありがとうございます。


またロード時に品番と前方一致の対比表が作れれば解決する方向性が見えてきました。

ただ、前方一致の項目の桁数が常に一定ではないため、

(例が悪かったのですが)

Left関数では出来ず、WildMatch?を使って同様のことが出来ないかと考えましたが、

やはり上手く出来ませんでした。。

Accessであれば、

where 品番 like 前方一致の中身(TAAA等) &'*'

といったやり方で前方一致の項目と品番の対比表が作れるようなのですが、

ロードスクリプトでも同様の書き方がございますでしょうか。

重ねての質問で恐縮ですが、

ご教授いただけましたら幸いです。

tfujioka
Partner - Contributor
Partner - Contributor

ロードスクリプトで売上データをロードするときに以下の項目を追加しました。

mid(品番,1,4) as 対象

前方一致が複数ある場合(※)の対応にはならないとは思いますが、例のようならば

こちらで問題ないかと思います。

※ 前方一致:TAAA 50万、TAA 30万、TA 10万

Not applicable
Author

Fujioka 様

ご返信いただきありがとうございます。

売上データの中で対象項目との紐付けを行えば実施できるというやり方ですね。

私の事例が悪かったので、訂正いたしましたが

前方一致の桁数が固定でないため、

実際のデータでは※に記載いただいていることも起こります。


もし良いやり方がございましたら

教えて頂けましたら幸いです。

tfujioka
Partner - Contributor
Partner - Contributor

さしあたりまして、品番と前方一致の対比表を手メンテ(ロード)する方法しか思いつきませんでした。

もうすでに検討済かと思いますが、共有させていただきます。

ykka-alto
Contributor
Contributor

ご連絡いただきました件につきまして、以下の関数を使用すると文字数に関係なく取得できるかと思います。

ロードスクリプトでLeft関数を使用している売上テーブルの按分品番を

SubField(品番,'-',1)

と変更ください。

ただ、この関数はハイフンなどの共通する記号がある場合のみに使用できます。

上記の例ではハイフンの左側の文字を取得する形になります。

Accessのような形で、Likeを使用した部分一致の条件はエクセルからの取り込みでは難しかったです。

Not applicable
Author

Fujioka 様

上記手法のご提示いただきましてありがとうございました。
実際に対比表を作成する方法で実施したいと思います。

どうもありがとうございました。

Not applicable
Author

Kusakabe 様

上記、ご回答ありがとうございました。

-(ハイフン)以外の場合もあり、実際には対比表を別途作成する方法で対応したいと思いますが、

SubField関数については今後活用していきたいと思います。


hka
Employee
Employee

そんなに難しく考えず、match関数とlenを使えばできます。

添付の右側のストレートテーブルの右端の数式を参照してください。

これで解決するのでは?

データはDBBBを編集してDBBも前方一致のデータに追加してます。

データ量が増えた場合のパフォーマンスには注意してください。