topic Re: convert date format to period in QlikView

convert date format to period

bharath28 — Thu, 10 Sep 2015 12:06:58 GMT

Hi All,

Here is my existing query which i run in qlikview script, to fetch data from oracle-

load *;

select * from transactionTbl

WHERE

DATE >= TO_DATE('$(vAppStartDate)', 'DD/MM/YYYY')

AND DATE <= TO_DATE('$(vAppEndDate)', 'DD/MM/YYYY');

variables-

--vAppStartDate = 01/06/2014

--vAppEndDate = 30/06/2015

Instead of using the Date column in the WHERE condition, i want to use PeriodID column existing in same table, to make the query faster.

The Period column is in below format.

PeriodID format--> 201294 //means 2012Q4. So 9 means Q

Since i am using variables, the values are always in date format, so my requirement is to first change date coming from variable to period format.

I wrote the pseudocode, because i am sure sure how to convert the date to periodID format in oracle. Also in the periodID 9 means Q.

select * from transactionTbl

WHERE

PeriodID >= period_function(TO_DATE('01-JUL-14', 'DD/MM/YYYY'), 'YYYYxx')

AND PeriodID >= period_function(TO_DATE('01-JUL-15', 'DD/MM/YYYY'), 'YYYYxx')

Please help me with the query.

Thanks!

jonathandienst — Thu, 10 Sep 2015 12:15:52 GMT

Assuming:

vAppStartDate = 01/06/2014

vAppEndDate = 30/06/2015

Then, before the SQL query

Let vPeriodStart = Year(vAppStartDate) & '9' & Num(Ceil(Month(vAppStartDate)/3), '00');

Let vPeriodEnd = Year(vAppEndDate ) & '9' & Num(Ceil(Month(vAppEndDate )/3), '00');

SQL

SELECT * From transactionTbl

Where PeriodID >= $(vPeriodStart)

And PeriodID <= $(vPeriodEnd);

If PeriodID is a a text field, add quotes:

SQL

SELECT * From transactionTbl

Where PeriodID >= '$(vPeriodStart)'

And PeriodID <= '$(vPeriodEnd)';

bharath28 — Thu, 10 Sep 2015 15:53:12 GMT

Thanks a lot!