Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to reduce ranges fo dates.. with a complication

Hi,

I have this problem (see attached data):

there is a table with ID and a dimension (C_QUAL) with some ranges of validity dates, where dates, typically, are consecutive. I would like to reduce the number of range choosing the min and the max date for every ID & C_QUAL.

The complication is that some dates are not consecutive: in this case (in red in the file), when the end date of the prevoius record is NOT the start date-1 of the successive record, I have to create a new range, even if C_QUAL is the same.

Is there a simple solution?

Thanks!!

1 Solution

Accepted Solutions
MarcoWedel

Hi,

maybe one solution might be:

QlikCommunity_Thread_250972_Pic1.JPG

tabTemp:

LOAD ID,

    START,

    END,

    C_QUAL

FROM [https://community.qlik.com/servlet/JiveServlet/download/1218614-267007/SAMPLE.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))));

table1:

LOAD *,

    RangeSum(Peek(RangeNo), -(ID<>Previous(ID) or C_QUAL<>Previous(C_QUAL) or START<>Previous(END)+1)) as RangeNo

Resident tabTemp

Order By ID, C_QUAL, START;

DROP Table tabTemp;

tabRanges:

LOAD RangeNo,

    Date(Min(START)) as RANGE_START,

    Date(Max(END)) as RANGE_END

Resident table1

Group By RangeNo;

hope this helps

regards

Marco

View solution in original post

2 Replies
MarcoWedel

Hi,

maybe one solution might be:

QlikCommunity_Thread_250972_Pic1.JPG

tabTemp:

LOAD ID,

    START,

    END,

    C_QUAL

FROM [https://community.qlik.com/servlet/JiveServlet/download/1218614-267007/SAMPLE.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))));

table1:

LOAD *,

    RangeSum(Peek(RangeNo), -(ID<>Previous(ID) or C_QUAL<>Previous(C_QUAL) or START<>Previous(END)+1)) as RangeNo

Resident tabTemp

Order By ID, C_QUAL, START;

DROP Table tabTemp;

tabRanges:

LOAD RangeNo,

    Date(Min(START)) as RANGE_START,

    Date(Max(END)) as RANGE_END

Resident table1

Group By RangeNo;

hope this helps

regards

Marco

Anonymous
Not applicable
Author

Very clever

Thank you very very much!