Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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.