Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jozisvk11
Creator
Creator

Variable time field length

Hello,

I have a problem with filtering certain time field in script load. I want to set it automatic.

My script is: (date format is 'MM. DD. YYYY h:mm:ss')

LET transaction_from = '10. 1. 2017 0:00:00';

LET transaction_to = '10. 31. 2017 23:59:59';

LET foundation_from = '11. 1. 2017 0:00:00';

[Transaction]:

Load

  [t_rbid]                     as [ID object],

  [t_leac]                     as [ID account],

  [t_trdt]                      as [Date transaction],

  [t_crdt]                      as [Date foundation],

  [t_crus]                      as [ID employee],

  [t_amnt]                     as  [Amount]

  ;

SQL SELECT

  *

FROM [dbo].[ttfgld482]

[t_trdt] >= '$(transaction_from)' and [t_trdt] <= '$(transaction_to)' and [t_crdt] >= '$(foundation_from)'

;

I want to rewrite date by some function in order to it goes automatic. I try to use function monthstart and monthend but it doesn´t work me.

Could you help me ?

Thank you

1 Solution

Accepted Solutions
jozisvk11
Creator
Creator
Author

The solution is:

LET transaction_from = Date(MonthStart(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET transaction_to = Date(MonthEnd(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET foundation_from = Date(MonthStart(Today()), 'MM. DD. YYYY h:mm:ss');

View solution in original post

1 Reply
jozisvk11
Creator
Creator
Author

The solution is:

LET transaction_from = Date(MonthStart(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET transaction_to = Date(MonthEnd(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET foundation_from = Date(MonthStart(Today()), 'MM. DD. YYYY h:mm:ss');