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
Adding to Maxgro's solution from above:
Use sum(Flag) in your chart expression with relevant dimension.
IF(cell_id <> Peek('cell_id) AND status = 'IN' AND on_line >0, 1,0) As Flag;
LOAD Time Stamp,
FROM <YourLogfilenameLocation path>;
If this doesn't work please share your script or a sample file to work on it?
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.
Data_Sample for qlik2..csv 12.8 MB
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
aa (2).qvw 6.2 MB
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:
[area number], [customer id],
If([area number] <> peek([area number]) and status = '*IN_NETWORK*' AND online >0,1,0) as Switch
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?
LOAD //rowno() as id,
[Data_Sample for qlik2..csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
left join (Source)
if(customer_id = peek('customer_id') and [area number] <> peek('area number'),1,0) as Flag
Where wildmatch(Status, '*IN_NETWORK*')
Order By customer_id, Date, Time;