topic Re: Getting a date from a column in a select, then using it in a text object after reformatting it in QlikView

Getting a date from a column in a select, then using it in a text object after reformatting it

Wed, 06 Aug 2014 18:52:49 GMT

Hi, I would like to select the top record from a SQL Server table, like SELECT TOP 1 Date FROM Table_X, and assign that value to a variable, then pull the month out from the date, and then for lack of a better word, substring the first three letters of the month, and then apply that three letter word to a text object field... anyone know how this can be done?

Thanks!

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

Wed, 06 Aug 2014 18:53:42 GMT

So something like 04/01/2014 would become -> April -> APR

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

simenkg — Wed, 06 Aug 2014 18:59:28 GMT

Upper(text(month(Date))) will give you that. If that gives you APRIL, then use

Left(Upper(text(month(Date))),3)

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

Wed, 06 Aug 2014 19:01:50 GMT

Ok great, what abou the load script. How would I pull a top value from a tablecolumn and assign it to a variable?

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

simenkg — Wed, 06 Aug 2014 19:04:56 GMT

Depends on how you mean.

If you have a table like

Dates:

load Date from Table1 order by Date desc;

then you can use

let vDate = peek('Date',0,'Dates');

Should give you the highest Date. If it gives you the lowest change desc to asc.

If you really ONLY need the top date, then you can load a date like this.

Dates:

Load max(Date) as MaxDate from Table1;

let vDate = peek('MaxDate',0,'Dates');

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

ashfaq_haseeb — Wed, 06 Aug 2014 19:08:03 GMT

After you made connection string with database

same SQL will work

SELECT TOP 1 Date FROM Table_X

Later use peek function to hold a value to variable.

Regards

ASHFAQ

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

Wed, 06 Aug 2014 19:17:54 GMT

Ok I must be missing something, here's what I got:

Date:

SQL SELECT TOP 1 REPORT_DATE
FROM Sales;
LET vMonth = peek('REPORT_DATE',0,'Date');

When I step thru the load, the value of vMonth is null, but it shows one record has been loaded into the table

Re: Getting a date from a column in a select, then using it in a text object after reformatting it

ashfaq_haseeb — Wed, 06 Aug 2014 19:21:08 GMT

Hi try below

LET vMonth = peek('REPORT_DATE',-1,'Date');