Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I fixed this issue by creating an ID and load data where ID>Previous ID-8
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
Hi,
The where statement is wrong.. it returns
Quarter
20111
20112
20113
20114
Which is not what I am looking for..
Thxs,
Alec
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...
Hi,
Thank you for your help but it is not working.. it doesn't return any fields..
Thxs,
Alec
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
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
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.
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
which field you want to filter ID or quarter?