topic Re: SQL: Group data when a value will be repeated for a ID and create a gapless date range in QlikView

SQL: Group data when a value will be repeated for a ID and create a gapless date range

daniel1990xyz — Fri, 08 Jul 2016 13:32:19 GMT

Hello everyone,

I would like to group data from a sql table as long as the value "TreatDataTableInfo" will not change. And I would link to use the oldest date from the group as the "FromDate" and the date from the next group/next data record with different "TreatDataTableInfo" -1day as the "ToDate". So in the end the groups should have a gap in the date range. Please check the screenshot for a example. I have highlighted two grouping examples.

I have tried to solve this problem by using sql (group by) and by using QlikView Load statements with peek() and so, but I can not find a solution.

Many thanks for any advice/help.

Best regards,

Daniel

Re: SQL: Group data when a value will be repeated for a ID and create a gapless date range

boorgura — Fri, 08 Jul 2016 14:10:32 GMT

You can the grouping in LOAD statement.

Table1:

LOAD Part_ID,

min(Date) as From_Date,

TreatDateTableInfo,

TreatID

from <sourcefile>

GROUP by Part_ID, TreatDateTableInfo, TreatID

ORDER BY Date

;

left join(Table1)

LOAD date(date#(From_Date)-1) as To_Date,

TreatDateTableInfo,

TreatID,

previous(From_Date) as From_Date

resident Table1;

Re: SQL: Group data when a value will be repeated for a ID and create a gapless date range

daniel1990xyz — Fri, 15 Jul 2016 04:59:59 GMT

Thank you Rakesh for your help. In the meanwhile I could solve the problem by using the following sql statement that I received by a Stack Overflow user.

qlikview - Collapse Data in Sql without stored precedure or function if a value is the same as the value from row above …