Qlik Community

Qlik Japan

Qlik Communityの日本語のグループです。 Qlik製品に関する日本語資料のダウンロードや質問を日本語で投稿することができます。質問に関する回答は基本的に日本語で行われます。 日本語資料はグループにJoinしなくてもダウンロード可能ですが、 質問を投稿したい場合は、CommunityへJoin Groupを行って下さい。 Join後に質問を投稿することが可能になります。 ログインはQlikIDを登録してください。

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
Highlighted
Partner
Partner

ロードスクリプト内での変数処理

はじめまして、電通国際情報サービスの星加と言います。いつもお世話になっております。

実はロードスクリプトで変数を使ってシーケンスナンバーを入れたいのですが、LOAD文の中でLETが使えないようなので困っています。

オリジナルのファイルはこのようなもので、あらかじめoperation_time順になっています。

operation_time, USER_NAME
09:00,Mr A
09:01,Mr A
09:03,Mr A
10:00,Mr B
10:30,Mr B
11:00,Mr B
15:00,Mr C
15:10,Mr C
15:15,Mr C
17:00,Mr A

これに対してPCの利用者が切り替わった事を判定して、Numberを与えていきたいのです。

operation_time, USER_NAME,Number
09:00,Mr A,1
09:01,Mr A,1
09:03,Mr A,1
10:00,Mr B,2
10:30,Mr B,2
11:00,Mr B,2
15:00,Mr C,3
15:10,Mr C,3
15:15,Mr C,3
17:00,Mr A,4

この時、時系列で同じ人が使っている場合は、上記のように同じ数値にしたく、人が切り替わった時にカウントアップをしたいのです。

単純に名前で Group By すると、最下部のAさんも1になってしまいます。ここはCさんの次なので4にしたいのです。

すごく簡単な事で恐縮なんですが、どなたかご教示くださいませんでしょうか? 宜しくお願いいたします。

PS 本当にやりたいことは

start,end,USER_NAME

09:00,09:03,Mr A

10:00,11:00,Mr B

15:00,15:15,Mr C

17:00,17:00,Mr A

といったテーブルを作成した上で、他のログとインターバルマッチするテーブルを準備したいだけなんですが、その手前で上記赤色の再度現れたAさんのテーブルを作るのが、どうもうまくいかなくて・・・・ 何か良い関数などあるのでしょうか?

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: ロードスクリプト内での変数処理

kiwaseaqkさんのアドバイスを基に集計キーを作成し、次にMinとMaxにて集計しなおすとPSに書いてあった表ができるようですがいかがでしょうか。

//Script

Tmp:

LOAD operation_time,

     USER_NAME,

     if(Previous(USER_NAME) = USER_NAME,numsum(peek('col_num') , 0),numsum(peek('col_num') , 1)) as col_num

FROM user.csv (txt, codepage is 932, embedded labels, delimiter is ',', msq);

LOAD Time(min(operation_time)) as start, Time(max(operation_time)) as end, USER_NAME

Resident Tmp

Group By USER_NAME, col_num

Order By col_num;

(ロード結果)

start,end,USER_NAME

09:00,09:03,Mr A

10:00,11:00,Mr B

15:00,15:15,Mr C

17:00,17:00,Mr A

View solution in original post

10 Replies
Highlighted
Not applicable

Re: ロードスクリプト内での変数処理

ダミーでも良いのでサンプルデータはありますか。

Highlighted
Not applicable

Re: ロードスクリプト内での変数処理

こんにちは、オーリック 岩瀬と申します。

自分は列に連番を振りたい場合は、ロードスクリプトに以下のような記述していますよ。

if ( Previous(col_abc) = col_abc,    numsum(   peek('col_num') , 1)   as col_num

本当にやりたいことの回答になっていないですが、キーブレークする連番を振るのであれば

上記で可能だと思います。

Highlighted
Partner
Partner

Re: ロードスクリプト内での変数処理

tigerさん、ありがとうございます。ただ通信データでして、20億件あるので、サンプルデータの作成がちょっと手つかずで、すみません。

岩瀬さんもありがとうございます。

ちなみに numsum(   peek('col_num') , 1)   の部分、実際には どういう動きをするのか教えていただけませんでしょうか?

私がべたべたに書くと、下記のように膨大になってしまいます・・・・・涙

要約としては
6行目  RowNoで番号を振る

20行目 利用IPが前の行と変わっていたら、その行のRowNo()を入れておく、でなければ0をセット

そうすると 
例) 10000600000 みたいな列ができる


それを今度は、何回もループしながら

2回目のループ 11000660000

3回目のループ 11100666000

4回目のループ 11110666600

5回目のループ 11111666660

6回目のループ 11111666666


みたいに、順次、前の行を違っていたら、前の数字を0の上に上書きする処理をして
すべての0がなくなるまでループさせて、終了

こういう汚いロジックになりました・・・・

もっとスパッと出来るロジックはないものでしょうか?


ループさせなくても、処理した直前の行の情報が判定できればいいのですが・・・・・

---------------------------------------------------------------------------------------------------------------------------------------

      1   Directory;

      2   DHCP:

      3   LOAD Distinct

      4

      5       'ALL' as ALL, // テーブル全体を定義したいから

      6       RowNo() as No, // 確認用

      7

      8        MACアドレス,

      9

      10        月 &'/'&if(len(日)=1,0&日,日) &' ' &時刻 As  DHCP_月日時刻,

      11

      12   //     サーバ名,

      13        MSG,

      14   //     on,

      15        利用IP,

      16   //     to,

      17        機器利用者,

      18        ホスト名,

      19

      20        if(利用IP<>Previous(利用IP), RowNo(),0) as IP切り替わりポインタ

      21

      22   FROM

      23   削除可_DHCP.qvd(qvd);  // ここでは絶対にWhereを付けてはいけない!!!!!!!! Previous関数を使っているから!!!!!! ★★★★★★★★

      24

      25   Store DHCP into Z:\削除可_DHCP2.qvd(qvd);

      26   //DROP Tables DHCP;

      27

      28

      29   TRACE ------------------------;

      30   TRACE ループ処理に入ります;

      31   TRACE ------------------------;

      32

      33

      34   LET v回数 = 1;  // 下記のDO LOOPの初回を認識したいため、1をセット

      35

      36

//******************************************************************************* ループ処理 開始

      37   DO;

      38

      39      QUALIFY *;

      40      Directory;

      41      別名:

      42      LOAD

      43           ALL,

      44           No,

      45           MACアドレス,

      46           DHCP_月日時刻,

      47           MSG,

      48           利用IP,

      49           機器利用者,

      50           ホスト名,

      51           IP切り替わりポインタ

      52      Resident DHCP;

      53      UNQUALIFY *;

      54

      55      DROP Table DHCP;

      56

      57      Directory;

      58      DHCP:

      59      LOAD

      60           別名.ALL as ALL,

      61           別名.No as No,

      62           別名.MACアドレス as MACアドレス,

      63           別名.DHCP_月日時刻 as DHCP_月日時刻,

      64           別名.MSG as MSG,

      65           別名.利用IP as 利用IP,

      66           別名.機器利用者 as 機器利用者,

      67           別名.ホスト名 as ホスト名,

      68

      69           if(別名.IP切り替わりポインタ=0,Previous(別名.IP切り替わりポインタ),別名.IP切り替わりポインタ) as IP切り替わりポインタ

      70

      71      Resident 別名 Order By 別名.No;    // Order By は常駐LOADでしか使えない

      72

      73     if $(v回数) >= 2 then  // 2回目以降の場合は、チェックテーブルを削除する。 1回目には存在しないため何もしない

      74         DROP Table チェック;

      75     end if

      76

      77

      78      チェック: // ユーザが切り替わった直後のみ数値が入るので、まだループ処理が必要な場合は、min(ユーザ切り替わりポインタ)がゼロになる

      79      LOAD

      80           ALL,

      81           min(IP切り替わりポインタ) as ゼロはまだ有るか

      82      Resident DHCP

      83      Group By ALL;

      84

      85

      86      LET v回数  = v回数 + 1; // 処理回数をカウント

      87

      88      TRACE ------------------------;

      89      TRACE 処理回数 = $(v回数);

      90      TRACE ------------------------;

      91

      92   //   store DHCP into Z:\削除可_DHCP準備.qvd(qvd);

      93   //   drop Table DHCP;

      94      drop Table 別名;

      95

      96   LOOP while peek('ゼロはまだ有るか')=0; // ゼロはまだあるか?のテーブルに、まだ0があればLOOPし、再処理する。全部処理すると1行目(1ユーザ目)が最小値になるのでLOOPを終了する

      97

//******************************************************************************* ループ処理 終了

      98

      99

      100

      101   store DHCP into Z:\削除可_DHCP準備.qvd(qvd);

      102   Drop Table DHCP;

      103

      104   TRACE ------------------------;

      105   TRACE ループ処理完了しました;

      106   TRACE ------------------------;

      107

      108   //************* あらためてロード

      109

      110      Directory;

      111      DHCP:

      112      LOAD

      113           ALL,

      114           No,

      115           MACアドレス,

      116           DHCP_月日時刻,

      117           MSG,

      118           利用IP,

      119           機器利用者,

      120           ホスト名,

      121           IP切り替わりポインタ

      122      FROM

      123      Z:\削除可_DHCP準備.qvd(qvd);

      124

      125

      126   store DHCP into 削除可_DHCP準備2.qvd(qvd);

      127   Drop Table DHCP;

      128

      129

      130   TRACE ------------------------;

      131   TRACE DHCP(IP)開始終了テーブル作成;

      132   TRACE ------------------------;

      133

      134

      135   Directory;

      136   DHCP:

      137   LOAD

      138        MinString(DHCP_月日時刻) As MIN_DHCP_月日時刻,

      139        maxString(DHCP_月日時刻) As MAX_DHCP_月日時刻,

      140        MSG,

      141        MACアドレス,

      142        利用IP,

      143        機器利用者,

      144        ホスト名

      145   FROM

      146   削除可_DHCP準備2.qvd

      147   (qvd)

      148   Group by MACアドレス,MSG,利用IP,機器利用者,ホスト名

      149   ;

Highlighted
Not applicable

Re: ロードスクリプト内での変数処理

kiwaseaqkさんの方法とほぼ同じですが、

Previous関数を使用すれば良いのでは?

Previous(USERNAME)で前の行の結果を取って、前の行と一致しなければPrevious(NUMBER) +1というような形であればいけるのでは。

Highlighted
Partner
Partner

Re: ロードスクリプト内での変数処理

tigerさん、お世話になります。

20行目および69行目がそれにあたるのですが

問題なのは、下記なんです。

A行で 処理した結果を入れた(※ 例えば、0だったところを、前行の6に変更)行を 連続して今度は

A+1行の処理の時に さっきのA行をPreviousで取り出しても、処理した結果(6)ではなく、オリジナルの結果(0)が返ってくるので、仕方なくループさせまくってます・・・・・

Prevoiusって、あくまでLOADしたオリジナルしか取り出さないようなのです・・・

私の理解が変なのかな・・・   でもきっと3行程度でこのロジックが書けると思うんですよね・・・QVってすごくかしこいから・・・・

Highlighted
Not applicable

Re: ロードスクリプト内での変数処理

kiwaseaqkさんのアドバイスを基に集計キーを作成し、次にMinとMaxにて集計しなおすとPSに書いてあった表ができるようですがいかがでしょうか。

//Script

Tmp:

LOAD operation_time,

     USER_NAME,

     if(Previous(USER_NAME) = USER_NAME,numsum(peek('col_num') , 0),numsum(peek('col_num') , 1)) as col_num

FROM user.csv (txt, codepage is 932, embedded labels, delimiter is ',', msq);

LOAD Time(min(operation_time)) as start, Time(max(operation_time)) as end, USER_NAME

Resident Tmp

Group By USER_NAME, col_num

Order By col_num;

(ロード結果)

start,end,USER_NAME

09:00,09:03,Mr A

10:00,11:00,Mr B

15:00,15:15,Mr C

17:00,17:00,Mr A

View solution in original post

Highlighted
Partner
Partner

Re: ロードスクリプト内での変数処理

すごく単純化して

A

----

1

0

0

0

0

6

0

0

0

A

---

1

1

1

1

1

6

6

6

6

にしたい場合って、みなさんは、どう書きますか???

私のやり方(ループしまくり方式)で本番DBを処理してるんですが、処理時間があまりにかかってしまい困っております

Highlighted
Partner
Partner

Re: ロードスクリプト内での変数処理

う!!!すごい!すごい!!

御面倒をおかけしますが、

if(Previous(USER_NAME) = USER_NAME,numsum(peek('col_num') , 0),numsum(peek('col_num') , 1)) as col_num

numsum(peek('col_num') , 0)

numsum(peek('col_num') , 1)

の意味を教えて頂けませんか?

peekとnumsum、どちらも詳しくなくって、マニュアルを読んでも、今一つで・・・・

Highlighted
Not applicable

Re: ロードスクリプト内での変数処理

こんにちは、岩瀬です。

peekは、レコード(行)のフィールド(列)を参照するものです。

どのレコードのどのフィールドというように指定して値を取得します。

peek("col_num")とすると1件前のレコードのcol_numというフィールド(列)の値を返してくれます。

書式は、peek("",行)で行を指定しないと前のレコードになります。

numsumは単純にパラメータすべてを合算するものです。

よって、

numsum(peek('col_num') , 0)は、前のレコードのcol_numの値を返します。

numsum(peek('col_num') , 1)は、前のレコードのcol_numの値に1加算したものを返します。