Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

conditional row merge, can it be done?

Hi everybody,

I have this problem and i can´t find a solucion. I have a table like this one:

AnimalNameDiagnosesVisit DateNext visit
DOGRockycough12-oct22-oct
DOGRockycough14-oct17-oct
DOGRockycough15-nov20-nov
DOGRockyrash18-nov19-nov
DOGDukerash20-nov25-nov
DOGDukerash26-nov28-nov
DOGDukecough27-nov30-nov

And I need to MERGE or JOIN the rows that are the same animal, name and diagnosis within in a short period of time (maybe two days) and get the first visit and last visit

For example, for the first two rows I can assume that is the same illness, so I can merge both and get the first and max date, in this case, the first row dates. But the third row is a new one even though it has the same illness, but is a month later.

And for the dog duke, it came back with a rash a the next day of his last visit, so I can assume that is the same illness, so is the first visit date (fifth row) and the last date on the sixth row.

The result should be something like this

AnimalNameDiagnosesStartEnd
DOGRockycough12-oct22-oct
DOGRockycough15-nov20-nov
DOGRockyrash18-nov19-nov
DOGDukerash20-nov28-nov
DOGDukecough27-nov30-nov

The first table is in order, so there is no need to check the whole table looking for rows to join.

I hope i made myself clear and I thank you all for your time. Any question i'll try to answer it as fast as i can

1 Solution

Accepted Solutions
MVP
MVP

Re: conditional row merge, can it be done?

pseudocode

1) make a new table (load resident) in this way

- load the table ordered by Animal, ........Next visit

- if the Animal, Name, Diagnoses of current row are the same of prevous row

(if(peek(Animal)=Animal and .......)

     check (always using peek or previous to get the values of the field in previous record) date difference

     calculate the 2 dates (2 new fields)

     flag the record

2) read the flagged records of the new table (use the 2 new date fields)

5 Replies
MVP
MVP

Re: conditional row merge, can it be done?

pseudocode

1) make a new table (load resident) in this way

- load the table ordered by Animal, ........Next visit

- if the Animal, Name, Diagnoses of current row are the same of prevous row

(if(peek(Animal)=Animal and .......)

     check (always using peek or previous to get the values of the field in previous record) date difference

     calculate the 2 dates (2 new fields)

     flag the record

2) read the flagged records of the new table (use the 2 new date fields)

Not applicable

Re: conditional row merge, can it be done?

Thank you, That's a very good starting point. I didn't know about peek and previous so let's give it a try

Not applicable

Re: conditional row merge, can it be done?

Thank you again for your guidance. I work it out and got this solution

Base_TB:

Load Animal,

       Name,

       Diagnoses,

       ´Visit Date´,

       ´Observation days´,

       RowNo()as ID,

       if( Name=previous(Name) and

           Diagnoses=previous(Diagnoses) and

           [Visit Date] <= (previous([Observation days])+2),     peek(ID),     RowNo()) as Flag;

LOAD * INLINE [

Animal,Name,Diagnoses,Visit Date,Observation days

DOG,Rocky,cough,12-10-2015,22-10-2015

DOG,Rocky,cough,14-10-2015,17-10-2015

DOG,Rocky,cough,15-11-2015,20-11-2015

DOG,Rocky,rash,18-11-2015,19-11-2015

DOG,Duke,rash,20-11-2015,25-11-2015

DOG,Duke,rash,26-11-2015,28-11-2015

DOG,Duke,cough,27-11-2015,30-11-2015

];

Final:

NoConcatenate

Load Animal,

       Name,

       Diagnoses,

       date(min(´Visit Date´)) as Min_Date,

       date(max(´Observation days´)) as max_Date,

       Flag

resident Base_TB Group by Flag,Animal,Name,Diagnoses;

MVP
MVP

Re: conditional row merge, can it be done?

Remember that usually, when you use peek or previous the order is important and if you change the order of the rows in your inline table maybe you get different result. Also, in Qlik, you can only order by in a resident load.

So I suggest to (this could be a load from a qvd, txt, database, etc....)

a:

load * inline [

.........

];

and then do resident load (using peek, previous) with a order by

b:

noconcatenate lod

..............

resident a

order by ............;

Not applicable

Re: conditional row merge, can it be done?

Yes, the order in this case is very important, but it is done in previous tables. This is one of the steps of a bigger project and the input table for this part is loaded in order.

Thanks

Community Browser