Date to grouped days

[#1]

Hello, I have a dates field example 01.01.2015, 02.01.2015...... 31.12.2015 but i wanna treat them as days like this 1-30, 31-60, 61-90...... >=151. I wanna treat 30 days each in group. what do i do?

[amit_saini]

Hi,

From your date filed you can create Day e,g Day(Date_Field)

and later try something like below:

load distinct

    *,

    dual(if(Day >=0 and Day <= 7, '0-7 days',

    if(Day >=8 and Day <= 14, '8-14 days',

    if(Day >=15 and Day <= 21, '15-21 days',

    if(Day >=22 and Day <= 28, '22-28 days',

    if(Day >=29 and Day <= 60, '29-60 days',

    if(Day >=61 and Day <= 90, '61-90 days',

    if(Day >=91 and Day <= 120, '91-120 days',

    if(Day >121, 'Above 121 days')))))))),

    if(Day >=0 and Day <= 7, 10,

    if(Day >=7 and Day <= 14, 20,

    if(Day >=15 and Day <= 21, 30,

    if(Day >=22 and Day <= 28, 40,

    if(Day >=29 and Day <= 60, 50,

    if(Day >=61 and Day <= 90, 60,

    if(Day >=91 and Day <= 120, 70,

    if(Day >121, 80))))))))

    ) as DayGroup,

Thanks,

AS

[amit_saini]

Hi,

See the attachment.

Thanks,

AS

[#4]

Hi,

You can use DayNumberOfYear() to get day number of the year.And use class function to make groups based on the day number.

like

Table1:

load *,DayNumberOfYear(Date)  as DayNumber inline

[

Date,.....(other fields)

12/1/2013

03/13/2013

11/04/2013

];

load class(DayNumber,30) as interval

resident Table1;

[jagan]

HI,

Try like this

LOAD

*,

If(DayNumberOfYear(DateFieldName) >= 151, '>=151',

     If(DayNumberOfYear(DateFieldName) > 120, '120-150',

     If(DayNumberOfYear(DateFieldName) > 90, '90-120',

     If(DayNumberOfYear(DateFieldName) > 60, '60-90',

     If(DayNumberOfYear(DateFieldName) > 30, '30-60', 

     If(DayNumberOfYear(DateFieldName) > 0, '1-30', 'N/A')))))) AS DateRange

FROM DataSource;

Regards,

Jagan.

[Anonymous]

Did you check this?

Buckets

Creating Buckets in the Load Script

Creating Aging Buckets within QlikView

[tamilarasu]

Hi,

Try like below,

Date:
Load *,
If(Day<31, '1-30', If(Day<61,'31-60',If(Day<91,'61-90',If(Day<121,'91-120',If(Day<151,'121-150','>=151'))))) as Duration;
Load *,
Date - Yearstart(Date)+1 as Day;

// Sample date.
Load
  Date(MakeDate(2016,12,31) - recno(),'DD.MM.YYYY') AS Date
Autogenerate (MakeDate(2016,12,31) - MakeDate(2016,01,01));

[#8]

Hello,

I have Date field as dd-mm-yyyy example [01.01.2015] not Days as 1,2....151

[#9]

Didn't work

[amit_saini]

You can create Days filed from date like Day(Date) as Day

Thanks,

AS