Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
daniel1990xyz
Contributor III
Contributor III

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

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.

TreatDataTableIssue.JPG

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

1 Solution

Accepted Solutions
daniel1990xyz
Contributor III
Contributor III
Author

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

View solution in original post

2 Replies
boorgura
Specialist
Specialist

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;

    


daniel1990xyz
Contributor III
Contributor III
Author

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