Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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