topic Re: Date bucket from date fields in QlikView

Date bucket from date fields

didierodayo — Fri, 16 Dec 2016 20:46:12 GMT

Hello,

I have the date list in the attached file below, I am trying to create a bucket list from the date field as follow.

'< 3Months'

'3 - 6Months'

'6 - 12 months'

'12 -24 Months'

'24 - 36 Months'

any suggestion is welcomed.

Thanks

rubenmarin — Fri, 16 Dec 2016 21:07:55 GMT

Hi Didier, as an idea you can use IntervalMatch:

Dates:

LOAD @1, RowNo() as NumDate

FROM

[.\date list.xls]

(biff, no labels, table is Sheet1$);

Ranges:

LOAD * Inline [

Min ,Max ,Text

1 ,2 ,'< 3Months'

3 ,6 ,'3 - 6Months'

7 ,12 ,'7 - 12 months'

13 ,24 ,'13 -24 Months'

25 ,36 ,'25 - 36 Months'

];

Inner Join IntervalMatch (NumDate)

LOAD Min, Max

Resident Ranges;

I don't know the real scenario, but another idea can be loading the min and/or max dates and extract the difference in months when loading.

didierodayo — Fri, 16 Dec 2016 21:52:39 GMT

Thanks Ruben.