topic Re: Comparing value in different rows and columns, combining rows in QlikView

Comparing value in different rows and columns, combining rows

shuck1985 — Wed, 05 Sep 2018 07:50:49 GMT

I have a table with the following columns

case number Timestamp Beginning Timestamps End Total Time

I want to do the following: if the case number is the same then compare end time of row 1 and beginning time of row 2. If the difference is less than 5mins, create a new row with case number, beginning timestamp of row 1, end timestamp of row 2 and total time (sum of row 1 and row 2 total time).

currently I am unsure how to even start so any hint or idea would be greatly appreciated.

Thanks in advance

Re: Comparing value in different rows and columns, combining rows

marcus_sommer — Wed, 05 Sep 2018 12:06:34 GMT

Within an ordered load you could with interrecord-functions like Peek() or Previous() ? check the values from other records to calculate anything and/or to create new fields. I think in your case you could create your needed/changed values within one ore more new fields and also setting a flag of 0 or 1 to identify your needed records.

After this you load this table resident by using the flag and concatenate the records to it again by replacing the origin fields with the new created ones.

- Marcus

Re: Comparing value in different rows and columns, combining rows

shuck1985 — Thu, 06 Sep 2018 06:10:29 GMT

thank you

I'll give that a shot and report back later.