Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
お世話になっております。
先日よりQlikViewを触り始めた初心者です。
下記、
ご存知の方がいらっしゃいましたら教えて頂きたくお願いいたします。
今取り組んでいるのは、
売上データと費用データを組み合わせて品番別の実際の売上金額を計算しようとしています。
売上データは、
品種,品番,売上金額となっております。
品種 | 品番 | 売上金額 |
---|---|---|
テレビ | TAAA-R | 150万円 |
テレビ | TAAB-N | 100万円 |
テレビ | TBBB-C | 50万円 |
冷蔵庫 | RBBB-C | 70万円 |
費用データは、
種別,対象,金額という構造になっています。
種別 | 対象 | 費用金額 |
---|---|---|
品種 | テレビ | 50万円 |
品番 | TAAA-R | 20万円 |
前方一致 | TAA | 50万円 |
前方一致 | RBBB | 10万円 |
費用データでは、
種別という項目で3種類に分かれており、
①品種全体にかかったのか、
②品番別にかかった費用なのか、
③そして品番の前方一致でかかった費用なのかを分類しています。
品番別のものは単純に集計して、
品種別のものは品種合計の売上に応じて按分しています。
困っておりますのは③の前方一致の場合で、
合致した条件に応じて対象の売上金額を算出し、品番別に按分したいと考えておりますが
やり方がわかりません。
※上記の例で行きますと下記の表になります。
品番 | 売上金額 | 品種別費用 | 品番別費用 | 前方一致費用 | 最終売上金額 |
---|---|---|---|---|---|
TAAA-R | 150万円 | 50万*(150/300)=25万 | 20万 | 50万*(150/250)=30万 | 75万 |
TAAB-N | 100万円 | 50万*(100/300)=16.66万 | 0 | 50万*(100/250)=20万 | 63.34万 |
TBBB-C | 50万円 | 50万*(50/300)=8.33万 | 0 | 0 | 41.67万 |
RBBB-C | 70万円 | 0 | 0 | 10万 | 60万 |
Loadスクリプトで解決すべき問題なのか、計算式で解決すべき問題なのか
いい方法がありましたらご教授いただきたくお願いいたします。
以上、よろしくお願いいたします。
7/14 説明がわかりづらかったので追記しました。 メッセージ編集者: 健嗣 松永
7/16 前方一致の桁数が一定でなく、事例が悪かったので一部訂正いたしました。 メッセージ編集者: 健嗣 松永
そんなに難しく考えず、match関数とlenを使えばできます。
添付の右側のストレートテーブルの右端の数式を参照してください。
これで解決するのでは?
データはDBBBを編集してDBBも前方一致のデータに追加してます。
データ量が増えた場合のパフォーマンスには注意してください。
一旦ロードスクリプト側で前方一致の対象と同じ粒度で、売上金額を集計します(按分売上テーブル)。
※TAAAなど左から4つ共通で取る場合であればleft関数を使用して集計します。添付ファイルではleft関数を使用しております。
集計したテーブルと前方一致費用のテーブルとを結合し、費用の項目を追加します。(按分前方一致費用項目)
上記で作成したテーブルは売上テーブルとは粒度が異なり、紐付けができないため、売上テーブルに粒度をあわせた按分品番項目を追加します。
画面側で上記で作成した項目を使用して
sum(按分前方一致費用)*sum(売上金額)/sum(按分売上金額)
と計算すると品番別に按分された金額が表示されるかと思います。
メインのコピーシートの品種別費用品番按分の数式に記載しております。
やりたいことと異なっておりましたら申し訳ありません。。。
Kusakabe 様
ご返信頂きましてありがとうございました。
ロードスクリプト側で品番と前方一致の項目を紐付けて
按分用の売上金額集計表を作成する方法大変参考になりました。
ありがとうございます。
またロード時に品番と前方一致の対比表が作れれば解決する方向性が見えてきました。
ただ、前方一致の項目の桁数が常に一定ではないため、
(例が悪かったのですが)
Left関数では出来ず、WildMatch?を使って同様のことが出来ないかと考えましたが、
やはり上手く出来ませんでした。。
Accessであれば、
where 品番 like 前方一致の中身(TAAA等) &'*'
といったやり方で前方一致の項目と品番の対比表が作れるようなのですが、
ロードスクリプトでも同様の書き方がございますでしょうか。
重ねての質問で恐縮ですが、
ご教授いただけましたら幸いです。
ロードスクリプトで売上データをロードするときに以下の項目を追加しました。
mid(品番,1,4) as 対象
前方一致が複数ある場合(※)の対応にはならないとは思いますが、例のようならば
こちらで問題ないかと思います。
※ 前方一致:TAAA 50万、TAA 30万、TA 10万
Fujioka 様
ご返信いただきありがとうございます。
売上データの中で対象項目との紐付けを行えば実施できるというやり方ですね。
私の事例が悪かったので、訂正いたしましたが
前方一致の桁数が固定でないため、
実際のデータでは※に記載いただいていることも起こります。
もし良いやり方がございましたら
教えて頂けましたら幸いです。
さしあたりまして、品番と前方一致の対比表を手メンテ(ロード)する方法しか思いつきませんでした。
もうすでに検討済かと思いますが、共有させていただきます。
ご連絡いただきました件につきまして、以下の関数を使用すると文字数に関係なく取得できるかと思います。
ロードスクリプトでLeft関数を使用している売上テーブルの按分品番を
SubField(品番,'-',1)
と変更ください。
ただ、この関数はハイフンなどの共通する記号がある場合のみに使用できます。
上記の例ではハイフンの左側の文字を取得する形になります。
Accessのような形で、Likeを使用した部分一致の条件はエクセルからの取り込みでは難しかったです。
Fujioka 様
上記手法のご提示いただきましてありがとうございました。
実際に対比表を作成する方法で実施したいと思います。
どうもありがとうございました。
Kusakabe 様
上記、ご回答ありがとうございました。
-(ハイフン)以外の場合もあり、実際には対比表を別途作成する方法で対応したいと思いますが、
SubField関数については今後活用していきたいと思います。
そんなに難しく考えず、match関数とlenを使えばできます。
添付の右側のストレートテーブルの右端の数式を参照してください。
これで解決するのでは?
データはDBBBを編集してDBBも前方一致のデータに追加してます。
データ量が増えた場合のパフォーマンスには注意してください。