Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

fikristar
Not applicable

Counting status change in certain cells

Hi Qlik experts,

I have log files that contain fields as follows: Time Stamp, device name, cell_id, region and status, on_line.

Cell identifies a sub are where the device is located.

I have a requirement to count status change for each device from one cell to another cell with the following criteria:

for each device name if cell id is different from the previous one and status is "IN" and on_line >0, then include this in the count. Otherwise don't count.

I am sure this is very simple but could someone suggest the right expression or script for this.

Thank

1 Solution

Accepted Solutions
maxgro
Not applicable

Re: Counting status change in certain cells

Source:

LOAD //rowno() as id,

  Date,

     Time,

     [network id],

     [area number],

     customer_id,

     type,

     Status,

     latency,

     region

FROM

[Data_Sample for qlik2..csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

left join (Source)

//NoConcatenate

LOAD //id,

  Date,

     Time,

     //[network id],

     [area number],

     customer_id,

     //type,

     //Status,

     //latency,

     //region,

     if(customer_id = peek('customer_id') and [area number] <> peek('area number'),1,0) as Flag

Resident Source

Where wildmatch(Status, '*IN_NETWORK*')

Order By customer_id, Date, Time;

9 Replies
maxgro
Not applicable

Re: Counting status change in certain cells

Do a resident load of your table order by device, timestamp (or maybe an id you can add to order the table records as you want).

Use the peek function to check if the device of the current row is the same as the previous row and if the sub changed; also check the status N and on_lie > 0. If all the check are ok, you can set a new column (flag) to 1.

In a chart, sum the flag

vishsaggi
Not applicable

Re: Counting status change in certain cells

Adding to Maxgro's solution from above:

Use sum(Flag) in your chart expression with relevant dimension.

LOAD *,

         IF(cell_id <> Peek('cell_id) AND status = 'IN' AND on_line >0, 1,0) As Flag;

LOAD Time Stamp,

          device name,

          cell_id,

          region,

          status,

          on_line

FROM <YourLogfilenameLocation path>;


If this doesn't work please share your script or a sample file to work on it?

fikristar
Not applicable

Re: Counting status change in certain cells

Hi,

It does not seem to be working. BTW, what is the difference between previous and peek in this case? can we use previous instead?

Please find attached the sample file. The header names are a little bit different which is called area number, but they are conceptually the same. I'd like to know the rate of area number change for each customer_id wit certain condition.  In this sample the status is IN_NETWORK.

Thanks,

fikristar
Not applicable

Re: Counting status change in certain cells

Hi Massimo,

Could you suggest the script here? 

Does it have to be resident load and need to order records by device_id and then time stamp?

Thanks

maxgro
Not applicable

Re: Counting status change in certain cells

I'm not sure to understand all your conditions.

I set the flag when the customer is the same and the area number changes and status IN_NETWORK

PFA

1.png

fikristar
Not applicable

Re: Counting status change in certain cells

Hi Maxgro,

Thanks for the showing the result with the example and the QVW file. I wonder if you could post the script here since I am still working on getting the professional license so I can't open the file.

What  have done in the script is after loading the full table I added resident load as below:

LOAD

  Date,Time,

  [area number], [customer id],

  online,status,

     If([area number] <> peek([area number]) and status = '*IN_NETWORK*' AND online >0,1,0) as Switch

     Resident device-status

     ORDER BY [device id],Date,Time;

But it does not give me correct result. It is either all 0 or 1. Anything missing in my script?

vishsaggi
Not applicable

Re: Counting status change in certain cells

May be try this ?

LOAD

  Date,Time,

  [area number], [customer id],

  online,status,

    If([area number] <> peek([area number]) and Wildmatch(status, '*IN_NETWORK*') AND online >0,1,0) as Switch

    Resident device-status

    ORDER BY [device id],Date,Time;

maxgro
Not applicable

Re: Counting status change in certain cells

Source:

LOAD //rowno() as id,

  Date,

     Time,

     [network id],

     [area number],

     customer_id,

     type,

     Status,

     latency,

     region

FROM

[Data_Sample for qlik2..csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

left join (Source)

//NoConcatenate

LOAD //id,

  Date,

     Time,

     //[network id],

     [area number],

     customer_id,

     //type,

     //Status,

     //latency,

     //region,

     if(customer_id = peek('customer_id') and [area number] <> peek('area number'),1,0) as Flag

Resident Source

Where wildmatch(Status, '*IN_NETWORK*')

Order By customer_id, Date, Time;

fikristar
Not applicable

Re: Counting status change in certain cells

Hi Maxgro

Many Thanks for this!