topic Re: Using Max Date during ODBC Connection in QlikView

Using Max Date during ODBC Connection

craig157 — Wed, 25 Nov 2020 16:16:04 GMT

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"

Re: Using Max Date during ODBC Connection

Gysbert_Wassenaar — Wed, 05 Jul 2017 15:53:59 GMT

Perhaps like this:



SQL
    SELECT "_OrgID", "Type", "Complete", "CreatedBy", "ActionDateStart", "Note"
    FROM  "Org to History" OH1
    INNER JOIN
          (SELECT "_OrgID", Max("ActionDateStart") as "MAX_ActionDateStart"
          FROM "Org to History"
          GROUP BY "_OrgID") OH2
    ON
          OH1."_OrgID" = OH2."_OrgID" AND OH1."ActionDateStart" = OH2.MAX_ActionDateStart"

Re: Using Max Date during ODBC Connection

antoniotiman — Wed, 05 Jul 2017 16:02:51 GMT

Hi Craig,

try this

Table

LOAD * From TABLE;

Inner Join LOAD Max(ActionDateStart) as ActionDateStart

Resident Table;

Regards,

Antonio

Re: Using Max Date during ODBC Connection

craig157 — Thu, 06 Jul 2017 08:29:24 GMT

Hi Gysbert,

Unfortunately received the following error:

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

Re: Using Max Date during ODBC Connection

craig157 — Thu, 06 Jul 2017 08:37:50 GMT

Hi Antonio,

Thanks for the response but this is part of my ODBC Connection Load and this is where I am struggling to correctly write this.

Cheers

Re: Using Max Date during ODBC Connection

antoniotiman — Thu, 06 Jul 2017 15:59:51 GMT

OrganisationHistory: //doesn't change

.........

Inner Join LOAD Max(ActionDateStart) as ActionDateStart

Resident OrganisationHistory;

Re: Using Max Date during ODBC Connection

antoniotiman — Thu, 06 Jul 2017 16:23:35 GMT

Or like this :

Select Max(ActionDateStart) as MaxDate from "Org to History";

LET vMaxDate = Peek('MaxDate');

OrganisationHistory:

Load ........;

Select .................

from "Org to History"

Where ActionDateStart = $(vMaxDate);

Re: Using Max Date during ODBC Connection

craig157 — Fri, 07 Jul 2017 13:04:09 GMT

Hi Antonio,

Seems to be breaking my Qlikview every time I try to debug this.

Can you just confirm that you mean like this:

Select Max("ActionDateStart") as MaxDate from "Org to History";

Let vMaxDate = Peek("MaxDate");

OrganisationHistory:

LOAD

[_OrgID],

[Type],

[Complete],

[CreatedBy],

[Note],

[ActionDateStart];

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

FROM "Org to History"

WHERE ActionDateStart = $(vMaxDate);

Re: Using Max Date during ODBC Connection

Kushal_Chawda — Fri, 07 Jul 2017 13:16:05 GMT

try this

let vMaxDate = date(today()-1,'DD/MM/YYYY');

OrganisationHistory:

LOAD

[_OrgID],

[Type],

[Complete],

[CreatedBy],

[Note],

[ActionDateStart];

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('$(vMaxDate)','DD/MM/YYYY');

Re: Using Max Date during ODBC Connection

antoniotiman — Fri, 07 Jul 2017 14:45:36 GMT

Yes.

It seems correct.

Re: Using Max Date during ODBC Connection

craig157 — Wed, 12 Jul 2017 09:24:29 GMT

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

Re: Using Max Date during ODBC Connection

Kushal_Chawda — Wed, 12 Jul 2017 09:27:25 GMT

Have you tried what I suggested?

Re: Using Max Date during ODBC Connection

craig157 — Wed, 12 Jul 2017 09:29:21 GMT

Hi Kushal,

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

Not really sure where the problem is...

Thanks though

Re: Using Max Date during ODBC Connection

Kushal_Chawda — Wed, 12 Jul 2017 09:31:57 GMT

what error you are getting?

Re: Using Max Date during ODBC Connection

craig157 — Wed, 12 Jul 2017 10:32:19 GMT

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')

Re: Using Max Date during ODBC Connection

craig157 — Wed, 12 Jul 2017 15:29:58 GMT

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?

Re: Using Max Date during ODBC Connection

craig157 — Wed, 12 Jul 2017 15:39:16 GMT

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

to_date(to_ char(