Year-Quarter Auto generate ID

[sunilkumarqv]

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 .

[kiranmanoharrode]

Use Recno() function for Creating ID

[#3]

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

[SunilChauhan]

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); 

[SunilChauhan]

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);

[jagan · Accepted solution]

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.

[sunilkumarqv]

Thanks jagan

[sunilkumarqv]

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 .

[jagan]

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.

[sunilkumarqv]

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.