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"

[Gysbert_Wassenaar]

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"

[antoniotiman]

Hi Craig,

try this

Table

LOAD * From  TABLE;

Inner Join LOAD Max(ActionDateStart) as ActionDateStart

Resident Table;

Regards,

Antonio

[craig157]

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.

[craig157]

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

[antoniotiman]

OrganisationHistory:    //doesn't change

.........

Inner Join LOAD Max(ActionDateStart) as ActionDateStart

Resident OrganisationHistory;

[antoniotiman]

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

[craig157]

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

[Kushal_Chawda]

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

[antoniotiman]

Yes.

It seems correct.