Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Finding maximum instance of a record

Hi All,

Bad title - not sure of the best way to explain this in one short sentance!

I have to load in a lot of records that have the same identfiers but different versions:

City           Supplier            ContractNo

A              1                      1

A              1                      2

A              1                      3

A              2                      1

In some of my charts I only want the records from each sequence that have the highest contract number to be used (in the case above it would be A/1/3 and A/2/1 records). For other charts I need all instances to be used.

What is the simplest way of identifying which record is the last in the appropriate sequence? I thought about creating a separate table in my script of City/Supplier/Max(ContractNo) and then re-joining that back to my Contracts fact table somehow, but not sure how that would work.

Looping through each record in the table and checking all other records? Seems expensive over a couple of million records...

I think I can solve this, but probably not in the most elegant of ways!

Any pointers appreciated.

Thanks,

Rory.

Tags (1)
1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: Finding maximum instance of a record

You can try and create a flag like:

Table1:

Load * Inline [

City,Supplier,ContractNo

A,1,1

A,1,2

A,1,3

A,2,1

A,2,3

A,2,2

B,1,3

C,1,3

B,1,2

C,1,1

B,1,1

C,1,2

];

Final:

Load *, if(Supplier=previous(Supplier) and City=previous(City), 0, 1) as LatestFlag

Resident Table1

Order by City, Supplier, ContractNo desc;

Drop Table Table1;


This will be a 1 for your latest contractNo and 0's for the rest. Please find attached.


Hope this helps!

1 Reply
jerem1234
Valued Contributor II

Re: Finding maximum instance of a record

You can try and create a flag like:

Table1:

Load * Inline [

City,Supplier,ContractNo

A,1,1

A,1,2

A,1,3

A,2,1

A,2,3

A,2,2

B,1,3

C,1,3

B,1,2

C,1,1

B,1,1

C,1,2

];

Final:

Load *, if(Supplier=previous(Supplier) and City=previous(City), 0, 1) as LatestFlag

Resident Table1

Order by City, Supplier, ContractNo desc;

Drop Table Table1;


This will be a 1 for your latest contractNo and 0's for the rest. Please find attached.


Hope this helps!