Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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...

View solution in original post