Auto Number

[#1]

Hi,

I have a variable Year_Quarter and would like to assign unique number to this for some calculation. I tried using auto number

AutoNumber(Year_Quarter) as Year_Quarter_No. The correct number would be FY2014Q2 as either highest number and FY2011Q2 lowest number.

However the output is incorrect

Year_Quarter	Year_Quarter_No
FY2013Q4	1
FY2014Q1	2
FY2012Q4	3
FY2012Q3	4
FY2014Q2	5
FY2013Q2	6
FY2013Q3	7
FY2013Q1	8
FY2011Q2	9
FY2011Q4	10
FY2012Q2	11
FY2011Q3	12
FY2012Q1	13

Thanks, Simky

[its_anandrjs]

Hi,

If Year_Quarter field has correct sequence then go for RowNo( ) function to give unique number like

Load

Year_Quarter

..

..

RowNo() as UniqueRowID

from Statement

Hope this helps

Thanks & Regards

[MK_QSL]

Try as below

Temp:

Load * Inline

[

  Year_Quarter

  FY2013Q4

  FY2014Q1

  FY2012Q4

  FY2012Q3

  FY2014Q2

  FY2013Q2

  FY2013Q3

  FY2013Q1

  FY2011Q2

  FY2011Q4

  FY2012Q2

  FY2011Q3

  FY2012Q1

];

Final:

Load *, AutoNumber(Year_Quarter,'Year_Quarter') as ID;

Load * Resident Temp Order By Year_Quarter;

Drop Table Temp;

[its_anandrjs]

Hi,

You can try some thing like

Temp:

Load * Inline

[

  Year_Quarter

  FY2013Q4

  FY2014Q1

  FY2012Q4

  FY2012Q3

  FY2014Q2

  FY2013Q2

  FY2013Q3

  FY2013Q1

  FY2011Q2

  FY2011Q4

  FY2012Q2

  FY2011Q3

  FY2012Q1

];

FinalTable:

LOAD

Year_Quarter,

RowNo() as id1,

RecNo() as id2,

Autonumberhash256 (Year_Quarter) as ID

Resident Temp;

Drop Table Temp;

Hope this helps

Thanks & Regards