If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I would like to merge multiple rows into one row while creating 2 addition columns. I have minute by minute data coming into Qlik and I would like to turn each minute-row with consecutive & matching values into one row and add 2 new columns to that row that will end up representing start and end time. I would prefer to do this in the chart functions (within the sheet) but if it must be done in the load editor, that's okay. Here is my use case: A machine is running for 10 minutes. Right now I see 10 separate rows of data with timestamp (being the only value that is changing) showing that the machine is running from 12PM to 12:10PM. Instead, I would like to see 1 row showing that the machine is running with 2 new columns for the start time (at 12) and the end time (at 12:10). If machine or status change, I would expect to see a new row with a new start and end time referring to the change.
Here is a sample of the data:
In my load editor, I am loading lots of column names from a .qvd file and have played around with using a peek function. in the chart functions, I have been using Above function. Please help 🙂 Thank you in advance!
T1: Load * Inline [ Machine,Time,Status 1,12:00,Running 1,12:01,Running 1,12:02,Running 1,12:03,Running 1,12:04,Running 1,12:05,Running 1,12:06,Running 1,12:07,Running 1,12:08,Running 1,12:09,Running 1,12:10,Running 1,12:11,Stopped 1,12:12,Stopped 1,12:13,Running 1,12:14,Running 1,12:15,Stopped 1,12:16,Stopped 1,12:17,Running 1,12:18,Running 1,12:19,Stopped ]; T2: NoConcatenate Load *, if(RowNo()=1 or Machine<>Previous(Machine),1, if(Status='Running' and Previous(Status)='Stopped',Peek(StatusFlag)+1,Peek(StatusFlag))) as StatusFlag Resident T1 Order by Machine,Time; Drop Table T1; T3: Load time(min(Time),'hh:mm') as StartTime, StatusFlag as ID, Machine, 'Run' as Status Resident T2 where Status='Running' Group by Machine,StatusFlag; Inner Join(T3) Load time(max(Time),'hh:mm') as EndTime, StatusFlag as ID, Machine Resident T2 where Status='Running' Group by Machine,StatusFlag; NoConcatenate T4: Load time(min(Time),'hh:mm') as StartTime, StatusFlag as ID, Machine, 'Stopped' as Status Resident T2 where Status='Stopped' Group by Machine,StatusFlag; Inner Join(T4) Load time(max(Time),'hh:mm') as EndTime, StatusFlag as ID, Machine Resident T2 where Status='Stopped' Group by Machine,StatusFlag; Drop Table T2; Concatenate(T3) Load if(EndTime=StartTime,Null(),EndTime) as EndTime, StartTime, ID, Status, Machine Resident T4; Drop Table T4; Note: When you create chart, sort your chart by Machine and then StartTime to see as expected
This seems like a solution that may work however I am loading the data using a REST connection as opposed to the inline solution you posted. Qlik doesn't recognize StatusFlag as a field when loading the data. Any idea how to get around this?
StatusFlag Is created during the next load after actual data load. So you just need to replace inline table with your actual data source. I would suggest to create QVD using data coming from REST connection then replace inline table here with that QVD
Thank you, it was just an issue with how I was naming the tables.. I have many other machine statuses that could occur so I have tried to change the script to check for when machine status changes (instead of just changing from run to down). What I have is not quiet working; it is adding +1 to StatusFlag for every row no matter what the machine status is. Below is part of the script...
if(RowNo()=1 or machine>Previous(machine) ,1,
if( machineStatus<> Previous(machineStatus),Peek(StatusFlag)+1,
Peek(StatusFlag))) as StatusFlag
And here is the outcome: