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: 
rdsuperlike
Creator
Creator

Variable in script

I have a standard version date that I want to use in all the tabs in the load statements.

I have created below table in the beginning in main and stored it in variable vVersionDate.

VERSIONDATE:

Load

VERSION_DATE ;

SQL

select distinct END_DT as VERSION_DATE from TIME_V where DAY_DATE = TRUNC(SYSDATE);

Let vVersionDate = peek('VERSION_DATE',0,VERSIONDATE);

But I am not able to make it work in all the tabs

I tried using below in the load statement in one of the tables. It doesnt work.

date($(vVersionDate)) as VERSION_DT,

I get below error

can anyone help me here.

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

It is most likely a formatting problem. Your date is coming back from the database as a fully formatted timestamp. You can try a few options and see which one works:

1. Try to simply convert the incoming date into a number:

Let vVersionDate = num(peek('VERSION_DATE',0,VERSIONDATE) );


2. If that doesn't work, then try to convert the full timestamp string into a date field, and then convert it into a number:


Let vVersionDate =

num(

     date#(

               peek('VERSION_DATE',0,VERSIONDATE),

               'M/D/YYYY hh:mm:ss TT'

     )

)


cheers,

Oleg Troyansky

Check out my book QlikView Your Business - now available to pre-order on Amazon!



View solution in original post

4 Replies
nagaiank
Specialist III
Specialist III

Try the following: (VERSIONDATE in quotes)

Let vVersionDate = peek('VERSION_DATE',0,'VERSIONDATE');

rdsuperlike
Creator
Creator
Author

doesnt work

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

It is most likely a formatting problem. Your date is coming back from the database as a fully formatted timestamp. You can try a few options and see which one works:

1. Try to simply convert the incoming date into a number:

Let vVersionDate = num(peek('VERSION_DATE',0,VERSIONDATE) );


2. If that doesn't work, then try to convert the full timestamp string into a date field, and then convert it into a number:


Let vVersionDate =

num(

     date#(

               peek('VERSION_DATE',0,VERSIONDATE),

               'M/D/YYYY hh:mm:ss TT'

     )

)


cheers,

Oleg Troyansky

Check out my book QlikView Your Business - now available to pre-order on Amazon!



Peter_Cammaert
Partner - Champion III
Partner - Champion III

The peek() seems to be working already, maybe only once in a while.

However, if vVersionDate contains 2/7/2015 12:00:00 AM, then simply using the variable value as a parameter to the date() function will produce an illegal script expression, like date(2/7/2015 12:00:00 AM) as VERSION_DT. QlikView cannot correctly interprete the parameter.

Again, use quotes to turn the value into a string which QlikView will be able to handle. Like

DIE_DETAIL:

LOAD

date('$(vVersionDate)') AS VERSION_DT,

:

Best,

Peter