Load data for Previous 8 quarters

[alec1982]

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

[datanibbler]

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

[alec1982]

Hi,

The where statement is wrong.. it returns

Quarter

20111

20112

20113

20114

Which is not what I am looking for..

Thxs,

Alec

[datanibbler]

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...

[alec1982]

Hi,

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

Thxs,

Alec

[datanibbler]

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

[alec1982]

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

[#8]

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.

[alec1982]

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

[#10]

which field you want to filter ID or quarter?