topic Re: How to avoid hard coding in QlikView

How to avoid hard coding

Anonymous — Fri, 17 Nov 2017 08:52:05 GMT

what is the best way to get the future data into the SQL query without hard coding the date in the SQL query while loading the SQL query in data loader?

below is my SQL query in which i have hard coded my date directly in order to get the future data .Plzz suggest?

SELECT AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT, SUM ( AL2.TXN_CNT ),

(case when (SUM ( AL2.TXN_CNT )) > 0 then 'Y' else 'N' end),AL2.PROCESS_DATE,

AL1.POE_CAT, (case when AL1.BUS_LINE = 'PAS' then 'PAS' when AL1.BUS_LINE = 'SMAF' then 'PAS'

when AL1.BUS_LINE = 'UMA' then 'PAS' when AL1.BUS_LINE = 'UMAT' then 'PAS' when AL1.BUS_LINE = 'BDI' then

'PAS' when AL1.BUS_LINE = 'SMEI' then 'PAS' when AL1.BUS_LINE = 'SMLC' then 'PAS' when AL1.BUS_LINE = 'DMA'

then 'PAS' when AL1.BUS_LINE = 'PWM' then 'PAS' when AL1.BUS_LINE = 'PWMT' then 'PAS' else 'Non PAS' end),

AL1.HH, AL1.BUS_LINE, AL1.CIT_STATE, AL1.CITY, AL1.ORIGIN, AL1.STATE, AL1.ZIP, AL1.RETIRE_INDIC, AL1.ACCT_TYPE,

AL1.ACCT_STATUS FROM PLVDBO.ACCOUNT AL1, PLVDBO.A_FIN_TXN_DTL AL2 WHERE ( AL1.ACCT = AL2.ACCT (+)) AND

((AL1.FIRST_FUNDED_DT BETWEEN TIMESTAMP '2015-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000' AND

AL2.PROCESS_DATE(+) BETWEEN TIMESTAMP '2015-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000' AND

AL2.COMM_TRADE_INDIC(+)='Y')) GROUP BY AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT, AL1.POE_CAT,

(case when AL1.BUS_LINE = 'PAS' then 'PAS' when AL1.BUS_LINE = 'SMAF' then 'PAS' when AL1.BUS_LINE = 'UMA'

then 'PAS' when AL1.BUS_LINE = 'UMAT' then 'PAS' when AL1.BUS_LINE = 'BDI' then 'PAS' when AL1.BUS_LINE = 'SMEI'

then 'PAS' when AL1.BUS_LINE = 'SMLC' then 'PAS' when AL1.BUS_LINE = 'DMA' then 'PAS' when AL1.BUS_LINE = 'PWM'

then 'PAS' when AL1.BUS_LINE = 'PWMT' then 'PAS' else 'Non PAS' end), AL1.HH,

AL1.BUS_LINE, AL1.CIT_STATE, AL1.CITY, AL1.ORIGIN, AL1.STATE, AL1.ZIP, AL1.RETIRE_INDIC,

AL1.ACCT_TYPE, AL1.ACCT_STATUS,AL2.PROCESS_DATE;

Re: How to avoid hard coding

Aurelien_Martinez — Fri, 17 Nov 2017 08:58:46 GMT

Hi,

You can create two variables like :

LET vDateStart = YearStart(Today()); //you can use addyears, addmonths...

LET vDateEnd = YearEnd(Today()); //you can use addyears, addmonths...

And in your SQL

sql Select [...]

AL1.FIRST_FUNDED_DT BETWEEN TIMESTAMP '$(vDateStart)' AND TIMESTAMP '$(vDateEnd)'

;

Aurélien

Re: How to avoid hard coding

Anonymous — Fri, 17 Nov 2017 09:28:39 GMT

I am getting this below error

Re: How to avoid hard coding

Aurelien_Martinez — Fri, 17 Nov 2017 09:41:29 GMT

You have an issue with the date format,

You can try :

LET vDateStart = Date(YearStart(Today()), 'YYYY-MM-DD');

LET vDateEnd = Date(YearEnd(Today(), 'YYYY-MM-DD');

to_date in your sql

Aurélien

Re: How to avoid hard coding

calexandru — Fri, 17 Nov 2017 09:42:51 GMT

Hi Surya,

This is most likely due to the fact that you need to provide a timestamp and the YearStart and YearEnd functions that Aurelien suggested as solutions return a date string.

You might want to wrap the functions as such:

LET vDateStart = Timestamp(YearStart(Today()), 'YYYY-MM-DD hh:mm:ss.fff')

LET vDateEnd = Timestamp(YearEnd(Today()), 'YYYY-MM-DD hh:mm:ss.fff')

Cheers,

Alex

Re: How to avoid hard coding

Anonymous — Fri, 17 Nov 2017 10:03:47 GMT

Hi Alex

Its working now..../can you explain me the working functionality of the above function

Re: How to avoid hard coding

calexandru — Fri, 17 Nov 2017 10:22:50 GMT

Glad it works,

Sure thing, timestamp() deals with formatting an expression in either the system timestamp format or a user defined format.

It takes two arguments with the second being optional.

first parameter must be a valid interpretation of either a date or timestamp and the second is the format you want to format the timestamp in.

In your case you have the following:

Today() which returns the date for the current day

YearStart() which returns the first date of the year for the parameter you provided, in this case

and lastly timestamp() converts the date into a timestamp

The code looks like this:

Today() -> 17/11/2017

YearStart(17/11/2017) -> 01/01/2017

Timestamp(01/01/2017, 'YYYY-MM-DD hh:mm:ss.fff') -> 2017-01-01 00:00:00.000

Regards,

Alex

Re: How to avoid hard coding

Anonymous — Fri, 17 Nov 2017 11:02:48 GMT

Thanks a lot Alex...Your explanation is crystal clear

Re: How to avoid hard coding

calexandru — Fri, 17 Nov 2017 11:04:02 GMT

My pleasure Surya