topic Problem with advanced loading of data in QlikView

Problem with advanced loading of data

dferreirogft — Thu, 20 Oct 2011 14:10:14 GMT

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.

Re: Problem with advanced loading of data

jonathandienst — Thu, 20 Oct 2011 14:55:46 GMT

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

Problem with advanced loading of data

dferreirogft — Fri, 21 Oct 2011 09:37:35 GMT

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