topic Re: max date in QlikView

max date

kmswetha — Thu, 10 Aug 2017 11:45:05 GMT

Hi,

I'm having a file name where I'm extracting date along with time stamp, using timestamp(timestamp(filename)). So far is good.

How ever when I say max of that field, it returns' -.'

Not sure whats happening.

Any suggestions pls?

Re: max date

dan_sullivan — Thu, 10 Aug 2017 11:49:26 GMT

is it possible that some of the data is null in your timestamp variable?

Re: max date

kmswetha — Thu, 10 Aug 2017 11:51:05 GMT

It is a single file, stored in a variable? So no chance of null.

THe movement I use Max, it doesn't work

Re: max date

nilesh_gangurde — Thu, 10 Aug 2017 11:52:48 GMT

there is possibility that timestamp could be in text format.

use timestamp# function and get actual timestamp format.

Regards,

Nilesh

Re: max date

kmswetha — Thu, 10 Aug 2017 11:53:07 GMT

ITS WORKING WHEN I USE MAX STRING

Re: max date

kmswetha — Thu, 10 Aug 2017 11:55:21 GMT

EVEN THEN ITS NOT WORKING

Re: max date

nilesh_gangurde — Thu, 10 Aug 2017 11:57:29 GMT

share example in qvw or qvf

Re: max date

antoniotiman — Thu, 10 Aug 2017 12:09:26 GMT

What is the Filename ?

Re: max date

kmswetha — Thu, 10 Aug 2017 12:15:21 GMT

for example you can use file name as

XYZ_20_11_2016_00_12.csv'

Re: max date

kmswetha — Thu, 10 Aug 2017 12:15:44 GMT

timestamp(timestamp#(left(right('XYZ_20_11_2016_00_12.csv',20),16),'DD_MM_YYYY_HH_MM'),'MM/DD/YYYY hh:mm:ss TT')

Re: max date

Colin-Albert — Thu, 10 Aug 2017 12:19:40 GMT

Has your date / timestamp data been loaded in the correct format or has it been loaded as text?

These posts may help.

Why don’t my dates work?

Get the Dates Right

Re: max date

kmswetha — Thu, 10 Aug 2017 12:34:26 GMT

THanks colin for sharing.

have seen this. But I have followed the same format

Re: max date

antoniotiman — Thu, 10 Aug 2017 12:37:10 GMT

Try

=timestamp(timestamp#(Mid('XYZ_20_11_2016_00_12',Index('XYZ_20_11_2016_00_12','_')+1),'DD_MM_YYYY_hh_mm'),'MM/DD/YYYY hh:mm:ss TT')

and use FileBaseName() (it purge .csv) instead of FileName()

Re: max date

sunny_talwar — Thu, 10 Aug 2017 12:38:29 GMT

The right expression is this

=TimeStamp(TimeStamp#(Left(Right('XYZ_20_11_2016_00_12.csv',20),16),'DD_MM_YYYY_hh_mm'),'MM/DD/YYYY hh:mm:ss TT')

M is for Month

m is for Minute

Re: max date

Colin-Albert — Thu, 10 Aug 2017 12:39:32 GMT

Use the function filetime('filename') to get the file time not timestamp.

File functions ‒ QlikView

Re: max date

kmswetha — Thu, 10 Aug 2017 12:44:50 GMT

actually it was file time earlier, but filetime is different from the time that is there in Name.Hence has to go by this

Re: max date

kmswetha — Thu, 10 Aug 2017 12:54:37 GMT

Hi Sunny,

I'm able to convert to time stamp. Problem here is, when use max of the expression its not returning anything

Re: max date

sunny_talwar — Thu, 10 Aug 2017 12:57:18 GMT

Can you show few screenshots of the issue?

Re: max date

Colin-Albert — Thu, 10 Aug 2017 13:00:30 GMT

What does num(your_timestamp) return?

Is the timestamp value in a data table or in a variable?

Can you display max(your_timestamp) in a test chart?

I find charts are the best way to evaluate and test expressions as you do not need to reload the data each time. Then once the expression is correct, you can move the expression into the load script.

Can you show your actual script / expressions?

Re: max date

jujucts — Thu, 10 Aug 2017 13:02:14 GMT

my two cents would be to replace _ by - for date and - by : for time like

Step 1

Keepchar('XYZ_20_11_2016_00_12.csv', ‘0123456789’) as str will keep 201120160012

Step 2

Mid(str,5,4) & ‘-‘ & mid(str,3,2) & ‘-‘ Left(str,2) & ’T‘ & mid(str,9,2) & ‘:’ & right(str,2) as str2

will format as 2016-11-20T00:12

Step 3

TimeStamp( TimeStamp#(str2 ,'DD_MM_YYYY_hh_mm'), 'MM/DD/YYYY hh:mm:ss TT') will format as timestamp

You can LOAD like

LOAD
*, TimeStamp( TimeStamp#(str2 ,'DD_MM_YYYY_hh_mm'), 'MM/DD/YYYY hh:mm:ss TT') as Date

;
LOAD
*, Mid(str,5,4) & ‘-‘ & mid(str,3,2) & ‘-‘ Left(str,2) & ’T‘ & mid(str,9,2) & ‘:’ & right(str,2) as str2

;
LOAD
*,
Keepchar('XYZ_20_11_2016_00_12.csv', ‘0123456789’) as str

From Table;

Drop fields str1, str2;

best regards

juju