Chart expression set analysis only works with flat file

[#1]

I have an expression that works when my data source is directly from the xlsx file, but not when the xlsx file is imported to a DB which is used as the source data.

Chart expression

Sum ({<version = {'$(=FirstSortedValue(version, -date))'} >} cases)

This should grab the version associated with the most recent date.  I can't use "Max(version)" instead of "FirstSortedValue(version, -date)" because version is not a number, but in the formate of a program version, e.g. "3.0.1.355".

Load statement that makes the chart expression work

LOAD date,

     version,

FROM

[\\ftp1\VersionDates.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load statement that makes the chart expression NOT work

Load

    Date(date, 'MM/DD/YYYY') as date,

    version;

SQL

    select

        convert(varchar(10), date, 120) as date,

        version

    from DB1..Version_Dates;

Any help would be greatly appreciated!

[swuehl]

date needs to be correctly parsed in as a date (i.e. having a numeric representation) to make FirstSortedValue() work (sort weight needs to be numeric).

Check that date field has a numeric representation (e.g. check the tags in table view, or using num(date)).

Maybe you need to use Date#() interpretation function

Date#(date, 'MM/DD/YYYY') as date

[#3]

I tried num() and Date#() functions with no luck.  I'm pretty sure "date" is being parsed as a number since it's showing up in MM/DD/YYYY format and if I make a list box for that field, the default sort is by Numeric Value.

[swuehl]

Aren't you explicitely converting your date to text in your SQL?

[#5]

Yes, it was converted to text (red) then back to date formate (blue) and it seems to be seeing it as a date as far as I can tell.

Load

    Date(date, 'MM/DD/YYYY') as date,

    version;

SQL

    select

        convert(varchar(10), date, 120) as date,

        version

    from DB1..Version_Dates;

I tried not specifying any data format in the load script like the example below, then using Date(), Date#() and Num() in the expression, but still no dice.

Load

    date,

    version;

SQL

    select

        date,

        version

    from DB1..Version_Dates;

If it helps, as it appears in the DB version, the dates look like this:

2012-06-27 00:00:00

Does that help?  Any other ideas?

[swuehl]

Date(date, 'MM/DD/YYYY') is not converting it back.

Please check

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

http://community.qlik.com/docs/DOC-3102

If your date as returned from your SQL looks like 2012-06-27,

try

LOAD

     Date(Date#( date, 'YYYY-MM-DD'),'MM/DD/YYYY') as date,

     version;

....

[#7]

Very useful documents!  I am saving them in case I need to refer to them again.

Unfortunately, I still have the issue.  I read all the pages and I tried your solution as well as this similar one, since the original format is timestamped:

Date(Date#( date, 'YYYY-MM-DD h:mm:ss'),'MM/DD/YYYY') as date

I'm beginning to wonder if this is something else besides date format, since what you gave me was very thorough.

Interestingly, I tried adding this to the load script to see if I could use a field value when there's only one row instead of the function FirstSortedValue to get around the issue:

LOAD date,
version as current_version
Resident DateTable
Where date = Max(date);

I was unable to make this work, since when I reloaded the data, the script crashed: "Execution of script failed.  Reload old data?"

[swuehl]

Could you upload your qvw here to the forum? Upload is available in advanced editor or when editing an existing posting.

[Gysbert_Wassenaar]

Your convert call in the sql statement creates a text field (varchar). You need to use the date# function if you want to create a date from it again. I suggest you kick out the convert call from the sql statement and just get date as it is:

SQL select date,version from ....

Or maybe cast(date as date) as date if you want to get rid of the time part of the datetime.

If you don't want to do that I suggest you take a good look at this page to make sure you get back the datetime format you expect.

[#10]

That didn't work either.  I don't know what's causing this, but at least I found a work around.  I associated all "version" strings to their row number and sorted by that.

Load statement:

//this table maps each date to a version

OriginalDateTable:

Load date,

    version;

SQL

    select

        date,

        version

    from DB1..Version_Dates;

//this table allows ordering versions by how recent they are

VersionNumTable:

Load version,

    RowNo() as version_num

Resident OriginalDateTable;

//this table maps each date to a week and month for graphical displays

DateTable:

Load date,

    WeekStart(date) as week_of,

    MonthStart(date) as month,

    version

Resident OriginalDateTable

Where not isnull(version);

drop table OriginalDateTable;

Chart expression:

Sum ({<version={'$(=FirstSortedValue(version, -version_num))'}>}cases)

Thanks for all your help everyone!