topic Re: Year-Quarter Auto generate ID in QlikView

Year-Quarter Auto generate ID

sunilkumarqv — Fri, 22 Aug 2014 04:47:51 GMT

Hi All,

Need small help to create Year quarter ID that automatically generate the IDs

Examples

Year-Quarter ID

2011-Q1 1

2011-Q2 2

2011-Q3 3

2011-Q4 4

2012-Q1 5

2012-Q2 6

2012-Q3 7

2012-Q4 8

If you see Year-Quarter Column we have year - Quarter selection .we want write a script need to auto generate IDs based on Year-Quarter instead of manually creating .

Re: Year-Quarter Auto generate ID

kiranmanoharrode — Fri, 22 Aug 2014 05:12:58 GMT

Use Recno() function for Creating ID

Re: Year-Quarter Auto generate ID

Fri, 22 Aug 2014 05:13:00 GMT

Hi,

To do this you can use AutoNumber() or RowNo() generate IDs

Like this

LOAD [Year-Quarter ID],AutoNumber([Year-Quarter ID]) as ID , RowNo() as ID1

FROM

D:\Vivek\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards,

Vivek

Re: Year-Quarter Auto generate ID

SunilChauhan — Fri, 22 Aug 2014 05:18:35 GMT

see the attached

or load below code in you qvw

LET vDateMin = Num(MakeDate(2010,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

//$(vDateMin) + RowNo() - 1 AS DateNumber,

Year(Date($(vDateMin) + RowNo() - 1)) &' - Q'&ceil(MonTh($(vDateMin) + RowNo() - 1)/3) AS [Year-Quarter] ,

Iterno() as ID

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Re: Year-Quarter Auto generate ID

SunilChauhan — Fri, 22 Aug 2014 05:19:46 GMT

use actually this for 2011 onwords

LET vDateMin = Num(MakeDate(2011,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

//$(vDateMin) + RowNo() - 1 AS DateNumber,

Year(Date($(vDateMin) + RowNo() - 1)) &' - Q'&ceil(MonTh($(vDateMin) + RowNo() - 1)/3) AS [Year-Quarter] ,

Iterno() as ID

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Re: Year-Quarter Auto generate ID

jagan — Fri, 22 Aug 2014 05:40:00 GMT

Hi,

Try like this

Data:

LOAD

If([Year-Quarter] <> Previous([Year-Quarter]), Alt(Peek('ID'), 1), Peek('ID') + 1) AS ID;

LOAD

FROM Data

ORDER BY DataFieldName;

Hope this helps you.

Regards,

Jagan.

Re: Year-Quarter Auto generate ID

sunilkumarqv — Fri, 22 Aug 2014 06:39:38 GMT

Thanks jagan

Re: Year-Quarter Auto generate ID

sunilkumarqv — Fri, 22 Aug 2014 07:22:35 GMT

one more question

QuartetID need gererate up to completed quarters,

If Quater not completed for month or is in middle of month then quarter id no need .

Re: Year-Quarter Auto generate ID

jagan — Fri, 22 Aug 2014 07:27:17 GMT

Hi,

Then try like this

Data:

LOAD

If(DataFieldName >= QuarterStart(Today()), Null(),

If([Year-Quarter] <> Previous([Year-Quarter]), Alt(Peek('ID'), 1), Peek('ID') + 1)) AS ID;

LOAD

FROM Data

ORDER BY DataFieldName;

Regards,

Jagan.

Re: Year-Quarter Auto generate ID

sunilkumarqv — Fri, 22 Aug 2014 07:49:07 GMT

this is not working asap

Exp:

Q,Month,ID

Q1 Apr ,1

Q1 May,2

Q1 Jun,3

Q2,Jul,4

So if you see above Exp i have Q1 ,for Q2 Quarter is not completed so no need ID4.

Re: Year-Quarter Auto generate ID

jagan — Fri, 22 Aug 2014 08:03:44 GMT

Hi,

I am not sure why it is not working, it should work becuase we are using this condition for dates

If(DataFieldName >= QuarterStart(Today()), Null(),

If possible attach some sample file.

Regards,

Jagan.

Re: Year-Quarter Auto generate ID

sunilkumarqv — Fri, 22 Aug 2014 08:56:38 GMT

So,What is here DataFieldName means

Re: Year-Quarter Auto generate ID

jagan — Fri, 22 Aug 2014 09:11:29 GMT

Hi,

DataFieldName is your date field.

Regards,

Jagan.