Using Max Date during ODBC Connection

[craig157]

Hi All,

New to Qlikview and doing some testing of ODBC Connection to Filemaker.

I am trying to see if it is possible to only pull the Latest Date value for the 'OrganisationHistory' for certain IDs.

I've been googling all day and not getting a solid answer.

Currently my Script works for downloading all the data; but it is pulling about 500k lines so hopefully only getting the latest Historic record should help drop the count a bit.

It is also grabbing from multiple tables on Filemaker also.

Please see below (apologies for the naming convention - I've just taken over this Database)

Ideally I would like to only get the 'Latest Date from [ActionDateStart]'

ODBC CONNECT TO *** (XUserId is *****, XPassword is ******);

Organisation:

LOAD

[CompanyName],

[cOrgID],

[cSalesPersonDisplay],

[Log Usersales],

[Source],

[c2014 User];

SQL SELECT "CompanyName", "cOrgID", "cSalesPersonDisplay", "Log Usersales", "Source", "c2014 User" FROM "Organisation";

OrganisationHistory:

LOAD

[_OrgID],

[Type],

[Complete],

[CreatedBy],

[Note],

[ActionDateStart];

SQL SELECT "_OrgID", "Type", "Complete", "CreatedBy", "ActionDateStart", "Note" FROM "Org to History"

[craig157]

It doesn't seem to like it so must be an issue within the fields it is trying to pull.

Suppose I will just have to pull through all the data and then add the expressions and create a new list box.

Thanks for your help though

[Kushal_Chawda]

Have you tried what I suggested?

[craig157]

Hi Kushal,

Yes I also tried your solution but another syntax error problem.

Not really sure where the problem is...

Thanks though

[Kushal_Chawda]

what error you are getting?

[craig157]

SQL##f - SqlState: 42000, ErrorCode: 8310, ErrorMsg: [FileMaker][FileMaker] FQL0001/(5:8): There is an error in the syntax of the query.

SQL SELECT "_OrgID", "Type", "Complete", "CreatedBy", "ActionDateStart", "Note"

FROM "Org to History"

where to_date(to_ char(ActionDateStart,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date('11/07/2017','DD/MM/YYYY')

[craig157]

Also tried like this:

OrganisationHistory:

LOAD

[_OrgID],

[Type],

[Complete],

[CreatedBy],

[Note],

Date(Max([ActionDateStart])) as ActionDateStart;

SQL SELECT "_OrgID", "Type", "Complete", "CreatedBy", "Note", "ActionDateStart"

FROM "Org to History";

This was sucessfully ran but now I can't see any data for "_OrgID" & "ActionStartDate"

Hopefully I must be missing something; should I be grouping these?

[craig157]

Tried this method again but it really doesn't like:

to_date(to_ char(