How to reduce ranges fo dates.. with a complication

[Anonymous]

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!!

[MarcoWedel · Accepted solution]

Hi,

maybe one solution might be:

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]

Very clever

Thank you very very much!