topic Re: Query on auto increment concept in QlikView

Query on auto increment concept

karthikeyan1504 — Mon, 23 Nov 2015 09:49:24 GMT

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.

Re: Query on auto increment concept

arasantorule — Mon, 23 Nov 2015 10:23:49 GMT

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.

Re: Query on auto increment concept

Anonymous — Mon, 23 Nov 2015 10:33:49 GMT

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

Re: Query on auto increment concept

karthikeyan1504 — Mon, 23 Nov 2015 10:42:30 GMT

I have no idea... Thats why posted here

Re: Query on auto increment concept

t_chetirbok — Mon, 23 Nov 2015 10:43:38 GMT

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;

Re: Query on auto increment concept

Anonymous — Mon, 23 Nov 2015 10:47:58 GMT

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

Re: Query on auto increment concept

karthikeyan1504 — Mon, 23 Nov 2015 10:50:45 GMT

No. It will not repeat

Re: Query on auto increment concept

settu_periasamy — Mon, 23 Nov 2015 11:03:53 GMT

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;

Re: Query on auto increment concept

Anonymous — Mon, 23 Nov 2015 11:06:58 GMT

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

Create a mannual excel for same

Re: Query on auto increment concept

swuehl — Mon, 23 Nov 2015 11:40:27 GMT

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.

Re: Query on auto increment concept

qlikviewwizard — Mon, 23 Nov 2015 13:45:27 GMT

Incremental Load in Qlikview - Sources

Re: Query on auto increment concept

Mark_Little — Mon, 23 Nov 2015 15:16:36 GMT

Hi,

swuehl approach is certainly the easiest approach and the way I would tackle this.

Mark

Re: Query on auto increment concept

karthikeyan1504 — Tue, 24 Nov 2015 03:35:29 GMT

Hi Swehl,

your solution also is correct. As I have got few discrepancies with my real data, the solution is not working properly.

Few student numbers are repeated for the same year.

Thanks for your help!!!

Re: Query on auto increment concept

swuehl — Tue, 24 Nov 2015 07:12:14 GMT

You can also use

LOAD *,

AutoNumber(Academic_Year, Student_No) as Year_of_Study

to handle multiple lines for a student in the same year. Still, the Academic_Year needs to be chronological / sorted per student.