topic Re: how to do a rolling 13 in SQl select statement? in QlikView

how to do a rolling 13 in SQl select statement?

Thu, 06 Jun 2013 14:48:15 GMT

Hi,

Here i am pulling the data from source and i want to get the data only from past 13 months rolling.

i was trying to use a variable to store this,

Let vRolling=date(MonthStart(AddMonths(Date(Today()),-13)))

then,

SQL SELECT *

FROM "Table_name" Where Date_Field>$(vRolling)

i am getting an error which says script line error, can some one help me whats wrong with the path?

I know that we cannot use qlikview functions during ODBC query but i tried.

Re: how to do a rolling 13 in SQl select statement?

jagan — Thu, 06 Jun 2013 15:21:05 GMT

Hi,

Let vRolling = date(MonthStart(AddMonths(Date(Today()), -13)), 'yyyy-m-d');

TEst:

SELECT

FROM TableName

WHERE Date_Field> '$(vRolling)';

This works for me, in the above let statement change date format with your date format.

Regards,

Jagan.

Re: how to do a rolling 13 in SQl select statement?

Anonymous — Thu, 06 Jun 2013 15:23:54 GMT

How is the format of the field Date_Field?

Date_Field = ddmmyyyy

Date_Field = dd-mm-yyyy;

then the variable vRolling be equal to the format Date_Field

you try these,

then,

SQL SELECT *

FROM "Table_name" Where Date_Field>'$(vRolling)';

Re: how to do a rolling 13 in SQl select statement?

Thu, 06 Jun 2013 15:39:23 GMT

I did change the format to which the source has which 'MM/DD/YYYY' no still i see the script line error.

Let vRolling=date(MonthStart(AddMonths(Date(Today()),-13)),'MM/DD/YYYY')

FYI- before this i gave a hard code value '03/04/2012' it was working fine Also this an ODBC query. Not sure if something needs to be tweaked arounf the format or the script?

Re: how to do a rolling 13 in SQl select statement?

Thu, 06 Jun 2013 15:44:31 GMT

Hi Alejandro,

Date_field format on the source is 'MM/DD/YYYY', i have tried to mtach the format to the let statement variable but no luck?

Re: how to do a rolling 13 in SQl select statement?

Anonymous — Thu, 06 Jun 2013 15:47:54 GMT

Let vRolling=date(MonthStart(AddMonths(Date(Today()),-13)),'MM/DD/YYYY');

Prueba:

SQL SELECT *

FROM "Table_name" Where Date_Field>'$(vRolling)';

Re: how to do a rolling 13 in SQl select statement?

jagan — Thu, 06 Jun 2013 15:48:27 GMT

Hi,

Can you attach the sample file with script and sample data?

Regards,

Jagan.

Re: how to do a rolling 13 in SQl select statement?

Thu, 06 Jun 2013 16:14:46 GMT

Yes, thats exactly what i have tried to do, but no luck.

Re: how to do a rolling 13 in SQl select statement?

Anonymous — Thu, 06 Jun 2013 16:19:51 GMT

you are missing semicolon

Let vRolling=date(MonthStart(AddMonths(date(today()),-13)),'MM/DD/YYYY');

$(_qvd_name):

SQL SELECT *

FROM "DIM_TR"."T_TRAN" where NC_TRANS_DT>='$(vRolling)';

Re: how to do a rolling 13 in SQl select statement?

Thu, 06 Jun 2013 16:30:08 GMT

Ohh god i am so sorry all through this time that was the only mistake i was making it was the semicolon. it is perfectly working thanks Alejandro.

Re: how to do a rolling 13 in SQl select statement?

Thu, 06 Jun 2013 16:41:12 GMT

Thanks Mohan it was the Semicolon that i was missing from the sytax but its working now.