Query on auto increment concept

[karthikeyan1504]

Hi all,

hic‌, gwassenaar‌, sunindia‌, tresesco‌, settu_periasamy‌

I have a data as shown below,

Academic_Year, Student_No

2012/2013, 001

2013/2014, 001

2014/2015, 001

2015/2016, 001

2010/2011, 002

2011/2012, 002

2012/2013, 002

2013/2014, 002

2009/2010, 003

2010/2011, 003

2011/2012, 004

2012/2013, 004

Now I have a requirement to create the Year of Study field for the students.

For example,

Academic_Year, Student_No, Year of Study

2012/2013, 001, 1

2013/2014, 001, 2

2014/2015, 001, 3

2015/2016, 001, 4

2010/2011, 002, 1

2011/2012, 002, 2

2012/2013, 002, 3

2013/2014, 002, 4

2009/2010, 003, 1

2010/2011, 003, 2

2011/2012, 004, 1

2012/2013, 004, 2

Could anyone please advise me how to derive the field Year of Study?

Thanks & Regards,

Karthikeyan.

[arasantorule]

Hi Karthik,

You may create a master table for academic year like below.

Academic_Year AY_Key

2009/2010, 001

2010/2011, 002

2011/2012, 003

2012/2013, 004

.

.

.

.

2100/2101, 109

Then include this key (SAY_Key) in the Student table as well.

Then Have another Table for Student's Starting Year as below

Academic_Year, Student_No, Key_For_Student_Start_Year

2012/2013, 001,004

2010/2011, 002,002

2009/2010, 003,001

2012/2013, 004,004

Then Comparing these two will give us the result.

year_of_study=(SAY_Key-Key_For_Student_Start_Year)+1;

Hope this helps.

Thanks.

[Anonymous]

Which formula/Metrics/Logic you use to calculate Year of study??

[karthikeyan1504]

I have no idea... Thats why posted here

[t_chetirbok]

Hello!

Hope it help

tmp:

load * Inline

[

Academic_Year, Student_No

2012/2013, 001

2013/2014, 001

2014/2015, 001

2015/2016, 001

2010/2011, 002

2011/2012, 002

2012/2013, 002

2013/2014, 002

2009/2010, 003

2010/2011, 003

2011/2012, 004

2012/2013, 004

]

;

left join (tmp)

load

min(left(Academic_Year,4)) as MAX_YEAR, Student_No

Resident T

Group by Student_No;

Table:

NoConcatenate

load

Academic_Year, Student_No, right(Academic_Year,4)-MAX_YEAR as [Year of Study] Resident tmp;

drop Table tmp;

[Anonymous]

Good, its mean you have to create a new filed..

One more things, sample data you posted is unique or repeating like are you having multiple enteries like:

Academic_Year, Student_No

2012/2013, 001

2013/2014, 001

2012/2013, 001

2013/2014, 001

2014/2015, 001

2015/2016, 001

[karthikeyan1504]

No. It will not repeat

[settu_periasamy · Accepted solution]

Hi Karthik,

Try this..

T1:
LOAD * INLINE [
    Academic_Year,  Student_No
    2012/2013, 001
    2013/2014, 001
    2014/2015, 001
    2015/2016, 001
    2010/2011, 002
    2011/2012, 002
    2012/2013, 002
    2013/2014, 002
    2009/2010, 003
    2010/2011, 003
    2011/2012, 004
    2012/2013, 004
];
NoConcatenate
Final:
LOAD *, if(Student_No=Previous(Student_No),Peek(Year1)+1,1) as Year1 Resident T1 Order by Student_No;


DROP Table T1;

[Anonymous]

Well, then in this case, you need to use looping with Peek()/Previous() combination

or

Create a mannual excel for same

[swuehl]

You can also use Autonumber() function:

T1: 

LOAD *,

          AutoNumber(recno(), Student_No) as Year_of_Study

INLINE [ 

    Academic_Year,  Student_No 

    2012/2013, 001 

    2013/2014, 001 

    2014/2015, 001 

    2015/2016, 001 

    2010/2011, 002 

    2011/2012, 002 

    2012/2013, 002 

    2013/2014, 002 

    2009/2010, 003 

    2010/2011, 003 

    2011/2012, 004 

    2012/2013, 004 

]; 

The input table records need to be sorted chronological per student.