Merging multiple rows into one and creating new columns

[kdaniels-obrien]

Hi,

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!

[Kushal_Chawda]

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

[kdaniels-obrien]

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?

[Kushal_Chawda]

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

[kdaniels-obrien]

That is what I'm currently doing now... I still get the error that it doesn't recognize StatusFlag

[kdaniels-obrien]

It errors at T4 * 

[Kushal_Chawda]

Can you post the script here?

[Kushal_Chawda]

Make sure that You have used correct table name while taking resident load

[kdaniels-obrien]

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... 

NoConcatenate
Load *,
if(RowNo()=1 or machine>Previous(machine) ,1,
if( machineStatus<> Previous(machineStatus),Peek(StatusFlag)+1,
Peek(StatusFlag))) as StatusFlag

And here is the outcome:  

[Kushal_Chawda]

Load *,
if(RowNo()=1 or machine<>Previous(machine) ,1,

Looks there is error in typing