Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Complex Lookup; Help Please

Hi,

I am currently  building an application that involves very large data sets along with quite a complex lookup.  I am not quite sure how to go about this and would be grateful for any advice/help.

Table A is the fixed table that I want to extract data from.

Table B is the table I want to map data onto; this table will be very large.

Table A

covtypeusrange1range21234date1date2
CGO0.118600061006200630001/01/201231/03/2012
CGO0.118100011001200130001/04/201231/12/9999
CVU18.140200021002200230001/01/201231/12/9999
CVU40.150300031003200330001/01/201231/12/9999
TGU0.11870071072073001/01/201231/12/9999
TVO18.14080081082083001/01/201231/12/9999
TVO40.15090091092093001/01/201231/12/9999
OGU0.11810011012013001/01/201231/12/9999
OGO18.14020021022023001/01/201231/12/9999
OVU40.15030031032033001/01/201231/12/9999

Table B

KeyCovTypeUsSizeAreaDate
1CGO16101/02/2012
2CGO16101/05/2012
3OGO20201/01/2012
4CVU25301/07/2012
5CVU42101/05/2012

In essence I am trying to lookup based on:

Cov = cov

Type = type

Us = us

Size being between range1 and range2

Area being equal to column 1, 2, 3, or 4

Date being between date1 and date 2

The data that needs to be returned is contained within columns 1, 2, 3 and 4 to give th following result:

KeyCovTypeUsSizeAreaDateLookup
1CGO16101/02/20126000
2CGO16101/05/20121000
3OGO20201/01/2012210
4CVU25301/07/20122200
5CVU42101/05/20123000

Once again any help would be appreciated on this as i'm not really sure of the best way to start.

Many thanks

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Complex Lookup; Help Please

Ted,

I think you can do this using intervalmatch. You need to to the matching more than once, since you are coping with date and size intervals.

It could then work like this (I needed to recreate some area values because I failed loading your column names '1', '2',... correctly from the WEB):

LOOKUP:

LOAD cov,

     type,

     us,

     range1,

     range2,

     100+floor(RAND()*10) as A1,

     200+floor(RAND()*10) as A2,

     300+floor(RAND()*10) as A3,

     400+floor(RAND()*10) as A4,

     date1,

     date2

FROM

[http://community.qlik.com/thread/55783?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

TOMATCH:

LOAD Key,

     Cov,

     Type,

     Us,

     Size,

     Area,

     Date

FROM

[http://community.qlik.com/thread/55783?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

inner join (TOMATCH) IntervalMatch (Size, Cov, Type, Us) LOAD range1, range2, cov as Cov, type as Type, us as Us Resident LOOKUP;

inner join (TOMATCH) Intervalmatch (Date, Cov, Type, Us) LOAD date1, date2, cov as Cov, type as Type, us as Us Resident LOOKUP;

left join (TOMATCH) LOAD cov as Cov, type as Type, us as Us, range1, range2, date1, date2, A1, A2, A3, A4 Resident LOOKUP;

drop table LOOKUP;

RESULT:

NoConcatenate LOAD *, pick(Area, A1,A2,A3,A4) as Result Resident TOMATCH;

drop table TOMATCH;

//drop fields date1, date2, range1, range2, A1,A2,A3,A4;

Hope this helps,

Stefan

3 Replies
MVP
MVP

Re: Complex Lookup; Help Please

Ted,

I think you can do this using intervalmatch. You need to to the matching more than once, since you are coping with date and size intervals.

It could then work like this (I needed to recreate some area values because I failed loading your column names '1', '2',... correctly from the WEB):

LOOKUP:

LOAD cov,

     type,

     us,

     range1,

     range2,

     100+floor(RAND()*10) as A1,

     200+floor(RAND()*10) as A2,

     300+floor(RAND()*10) as A3,

     400+floor(RAND()*10) as A4,

     date1,

     date2

FROM

[http://community.qlik.com/thread/55783?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

TOMATCH:

LOAD Key,

     Cov,

     Type,

     Us,

     Size,

     Area,

     Date

FROM

[http://community.qlik.com/thread/55783?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

inner join (TOMATCH) IntervalMatch (Size, Cov, Type, Us) LOAD range1, range2, cov as Cov, type as Type, us as Us Resident LOOKUP;

inner join (TOMATCH) Intervalmatch (Date, Cov, Type, Us) LOAD date1, date2, cov as Cov, type as Type, us as Us Resident LOOKUP;

left join (TOMATCH) LOAD cov as Cov, type as Type, us as Us, range1, range2, date1, date2, A1, A2, A3, A4 Resident LOOKUP;

drop table LOOKUP;

RESULT:

NoConcatenate LOAD *, pick(Area, A1,A2,A3,A4) as Result Resident TOMATCH;

drop table TOMATCH;

//drop fields date1, date2, range1, range2, A1,A2,A3,A4;

Hope this helps,

Stefan

Not applicable

Re: Complex Lookup; Help Please

Many thanks for this, it seems to do just the job.

I will scale it up to the large database and see what happens.

nicolaipj
Contributor

Re: Complex Lookup; Help Please

Hi

This will also produce the result table, but Stefan's might be a litle nicer:

Table_A:

LOAD cov,

     type,

     us,

     range1,

     range2,

           1 as Column,

     [1] as Lookup,

     date1,

     date2

FROM [230217.xlsx] (ooxml, embedded labels, table is A);

LOAD cov,

     type,

     us,

     range1,

     range2,

           2 as Column,

     [2] as Lookup,

     date1,

     date2

FROM [230217.xlsx] (ooxml, embedded labels, table is A);

LOAD cov,

     type,

     us,

     range1,

     range2,

           3 as Column,

     [3] as Lookup,

     date1,

     date2

FROM [230217.xlsx] (ooxml, embedded labels, table is A);

LOAD cov,

     type,

     us,

     range1,

     range2,

           4 as Column,

     [4] as Lookup,

     date1,

     date2

FROM [230217.xlsx] (ooxml, embedded labels, table is A);

// Load Table B

Table_B:

LOAD Key,

     Cov,

     Type,

     Us,

     Size,

     Area,

     Date

FROM [230217.xlsx] (ooxml, embedded labels, table is B);

// Join Tabel A data on, and afterwards keep the rows that fulfill the criterias

Left join (Table_B)

Load cov as Cov,

     type as Type,

     us as Us,

     Column as Area,

     Lookup,

     range1, range2, date1, date2

resident Table_A;

NoConcatenate

Table_B_Final:

Load *

resident Table_B

where

Size >= range1 and

Size <= range2 and

Date >= date1 and

Date <= date2;

drop fields range1, range2, date1, date2 from Table_B_Final;

drop tables Table_A, Table_B;

How to attach?

/Nicolai

Community Browser