Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart expression set analysis only works with flat file

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!

9 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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;

....

Not applicable
Author

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
MVP
MVP

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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!