topic Re: Maxstring in QlikView

Maxstring

Fri, 10 May 2013 16:50:23 GMT

I have a date field , which has 4 dates and i need to load data based on latest date , some how maxstring(date) does not work in the script , but if i use same maxstring(date) in UI in a list box expression it works , i mean it gives the maximum date value ... can i know what could be the reason and how i can fix this.I am not sure what datatype of column it is in database.Tried max(date) too.

Thanks

Re: Maxstring

rustyfishbones — Fri, 10 May 2013 16:58:00 GMT

Sometimes Date Function in Qlikview don't work because you need to define the Date Format

for it to work

So try this maybe:

hope it works for you

Re: Maxstring

Fri, 10 May 2013 17:09:30 GMT

i tried that also , unfortunately the data is not in one format ,

few dates are like M/D/YY , some are MM/DD/YYYY , i tried both formats but it does not work.

There is only one value some old date in M/D/YY format , which i dont need anyways , rest all are in MM/DD/YYYY

Re: Maxstring

rustyfishbones — Fri, 10 May 2013 17:40:07 GMT

Ok so when you have a field that contains 2 formats and you need to convert to 1

You can try the ALT Function

So it would look like this for the Date field and then you can try the MAX(Date)

Re: Maxstring

Fri, 10 May 2013 18:24:01 GMT

ok so using the above syntax i am able to get all my values of date column in same format , but still unable to load data where date = max(date)

Re: Maxstring

rustyfishbones — Fri, 10 May 2013 18:28:51 GMT

I am not sure what the problem is

where any of my posts helpful, please give 5 points if helpful 🙂

Re: Maxstring

calvindk — Fri, 10 May 2013 19:30:53 GMT

Please post a data reduced sample qvw, so we can have a look at the script.

Re: Maxstring

Fri, 10 May 2013 21:18:39 GMT

Attached sample data , i want data to be loaded only for max date which is 4/26

Re: Maxstring

calvindk — Sat, 11 May 2013 12:22:11 GMT

This should do it.

BASE:

LOAD Max_Ext_Date,

CR#,

FROM

C:\Users\tv04\Desktop\test.xls

(biff, embedded labels, table is Sheet1);

INNER JOIN(BASE)

Load Max(Max_Ext_Date) as Max_Ext_Date

FROM C:\Users\tv04\Desktop\test.xls

(biff, embedded labels, table is Sheet1);

Edit: had a brainbleed..