Problem with advanced loading of data

[dferreirogft]

Hello,

I'm developing a qlikview application and i'm stuck.

I have 1 tabla tat have at least 3 important fields: ID, Compliance and Version.

In order to know if a ID is compliant, I have to search if this ID is repeated with an greater version number. If yes, then if this last ID is non-compliant then the first ID is non-compliant too.

I'm loading the database from an excel file.

I try to use the searchfor function in macro code, and lookup function in script code but I can't archive my objective...Anybody can help me?

Thanks in advance.

[jonathandienst · Accepted solution]

Hi

This how I've done this in the past:

Data:

LOAD ID, Compliance AS tmpCompliance, Version

FROM Source.xls .... ;

Left Join (Data)

LOAD ID, Max(Version) AS MaxVersion

Resident Data

Group By ID;

Left Join (Data)

LOAD ID, tmpCompliance AS Compliance

Resident Data

Where Version = MaxVersion;

Drop Field tmpCompliance, MaxVersion;

You may be able to optimise it a little with FirstSortedValue, but I did not need to in my case.

Hope that helps

Jonathan

[dferreirogft]

Thank you very much, this is exactly what i'm looking for.