topic Re: Auto Number in QlikView

Auto Number

Thu, 20 Mar 2014 20:12:33 GMT

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

Re: Auto Number

its_anandrjs — Thu, 20 Mar 2014 20:20:04 GMT

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

Re: Auto Number

MK_QSL — Thu, 20 Mar 2014 20:22:35 GMT

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;

Re: Auto Number

its_anandrjs — Thu, 20 Mar 2014 20:30:37 GMT

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