topic Re: Date intervals in QlikView

Date intervals

MindaugasBacius — Mon, 21 Aug 2017 10:08:35 GMT

Hello,

Need some help with creating date intervals.

Data:

The result wanted:

The example attached.

Thank you in advance!

Re: Date intervals

vinieme12 — Mon, 21 Aug 2017 10:17:54 GMT

what is the logic here?

Re: Date intervals

its_anandrjs — Mon, 21 Aug 2017 10:21:17 GMT

Try this way

Table:
LOAD * INLINE [
A, F2, Date
A, 1, 2017.01.01
A, 1, 2017.01.02
A, 1, 2017.01.03
A, 2, 2017.01.04
A, 1, 2017.01.05
A, 1, 2017.01.06
B, 3, 2017.01.01
B, 4, 2017.01.02
];

Left Join(Table)
LOAD
F2,
Max(Date) as Maxdate,
Min(Date) as MinDate
Resident Table
Group By F2;

Re: Date intervals

MindaugasBacius — Mon, 21 Aug 2017 10:34:04 GMT

Same fields A and F2 need to have aggregated min and max values.

The order of the Date is critical here.

Rows 2-3-4 and 6-7 have the same data A 1 but according to the Date field there have to be different Min and Max dates.

Re: Date intervals

MindaugasBacius — Mon, 21 Aug 2017 10:34:51 GMT

Unfortunately, this model cannot be applied as we have data like this:

Re: Date intervals

vinieme12 — Mon, 21 Aug 2017 10:37:16 GMT

Check attached app, need to create a flag in script

Re: Date intervals

its_anandrjs — Mon, 21 Aug 2017 10:38:11 GMT

Ok but how you differentiate the 6,7 rows that has values same but different dates in this case max will be 2001.01.06 and min 2017.01.01.

Re: Date intervals

MindaugasBacius — Mon, 21 Aug 2017 10:41:08 GMT

I was so stuck with solving this issue in back end that I have forgotten an easy front end solution.

Thank you!