Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kdaniels-obrien
Partner
Partner

Merging multiple rows into one and creating new columns

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: 

sample data 1.pngexpect sample.png

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!

13 Replies
Kush
MVP
MVP

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
Partner
Partner
Author

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?

Kush
MVP
MVP

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
Partner
Partner
Author

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

kdaniels-obrien
Partner
Partner
Author

It errors at T4 * 

Kush
MVP
MVP

Can you post the script here?

Kush
MVP
MVP

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

kdaniels-obrien
Partner
Partner
Author

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:  qlik machine.png

 
Kush
MVP
MVP

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

Looks there is error in typing