topic Re: Load data for Previous 8 quarters in QlikView

Load data for Previous 8 quarters

alec1982 — Wed, 20 Nov 2013 15:50:58 GMT

Hi guys,

What would be the best logic to use to load data for last 8 quarters outside of the current quarter.. If we are currently on 20134. then we need to load data starting from 20114

The quarter field looks like below..

Quarter

20111

20112

20113

20114

20121

20122

20123

20124

20131

20132

20133

20134

Re: Load data fro Previous 8 quarters

datanibbler — Wed, 20 Nov 2013 15:59:56 GMT

Hi Badr,

just use a WHERE_clause in your LOAD picking out the number (the 4th) that needs to be different - 1 instead of 3 in this case (8 quarters, 2 years) - calculate year(today()) and pick the corresp. number from there and just subtract 2.

Like this:

>>> WHERE MID([quarter_field], 4, 1) = (RIGHT(YEAR(TODAY()), 1) - 2) <<<

HTH

Best regards,

DataNibbler

Re: Load data for Previous 8 quarters

alec1982 — Wed, 20 Nov 2013 16:10:33 GMT

Hi,

The where statement is wrong.. it returns

Quarter

20111

20112

20113

20114

Which is not what I am looking for..

Thxs,

Alec

Re: Load data for Previous 8 quarters

datanibbler — Wed, 20 Nov 2013 16:17:14 GMT

Ah, sorry,

you have to add one more - the year is correct, you just have to select the right quarter, so it will become

>>> WHERE MID([quarter_field], 4, 1) = (RIGHT(YEAR(TODAY()), 1) - 2)

and MID([quarter_field], 5, 1) = [[current_quarter]] <<<

Dou you have a quarter_field in a master_calendar or so so you can compare with the current quarter?

I'm afraid QlikView has no such built-in function - it does have quarter-related functions, but they return something different. Of course, you could use a mapping_table...

Re: Load data for Previous 8 quarters

alec1982 — Wed, 20 Nov 2013 16:26:09 GMT

Hi,

Thank you for your help but it is not working.. it doesn't return any fields..

Thxs,

Alec

Re: Load data for Previous 8 quarters

datanibbler — Wed, 20 Nov 2013 16:29:43 GMT

Do you have a field with the current quarter? What does that look like?

=> For that WHERE to work, you'd need a field saying 1, 2, 3, 4

Re: Load data for Previous 8 quarters

alec1982 — Wed, 20 Nov 2013 16:38:01 GMT

The field that I have is called Quarter amd the values in it is what's showing on top..

I also have a field named Current Quarter which holds the current Quarter Value as follow:

Current Qr

20134

Thxs again for all your help!

Alec

Re: Load data for Previous 8 quarters

Wed, 20 Nov 2013 17:41:53 GMT

Try this I'm assuming Quarter is a number field and 8 quarters is 766 days.

if you want 2 quarters by changing 766 to 180 you will get last two quarters.

where Quarter >= year(today()-766)& if(left(QuarterName(today()-766),3)='Jan',1,if(left(QuarterName(today()-766),3)='Apr',2,if(left(QuarterName(today()-766),3)='Jul',3,

if(left(QuarterName(today()-766),3)='Oct',4))))

hope it helps.

Re: Load data for Previous 8 quarters

alec1982 — Wed, 20 Nov 2013 18:02:35 GMT

It didn't work..

Assuming I have ID field on the same table starting by 1 and ends by 10

how can I do the where statement to be like:

Where [HCandMetrics ID]>lastvalue([HCandMetrics ID])-8

Thxs,

Alec

Re: Load data for Previous 8 quarters

Wed, 20 Nov 2013 18:55:53 GMT

which field you want to filter ID or quarter?

Re: Load data for Previous 8 quarters

alec1982 — Wed, 20 Nov 2013 18:58:22 GMT

Trying to find a way around it since Quarter is not working..

Thxs,

Alec

Re: Load data for Previous 8 quarters

alec1982 — Fri, 22 Nov 2013 04:33:43 GMT

I fixed this issue by creating an ID and load data where ID>Previous ID-8

Re: Load data for Previous 8 quarters

Sat, 25 Jul 2015 19:56:41 GMT

Hi Alec i have same requirement can you attach sample file please