topic Re: Lookup or matching function in QlikView

Lookup or matching function

Fri, 25 Nov 2011 16:55:17 GMT

Hi,

I would like to know whether it is possible, and how, to lookup values or text.

What i am specifically lookin for, is a way to load an excel file or such, with a list of ids, lets call them 1,2,3,4.

Qv is then to look in a masterdatabase for these ids, and for each id return a list of subfunctions specified in the masterdb.

Pease let me know if you need more info.

/Rune

Lookup or matching function

swuehl — Fri, 25 Nov 2011 18:18:06 GMT

It probably depends on your concrete data model and what you want to achieve, I can think of at least 3 functions / methods that may help you:

1) Check out the lookup() function in the help, though this is probably not what you want.

2) You could use a mapping table (Check out Mapping Load and applymap() function in the help).

These two ideas are more for 1:1 relations, if you want to have 1:n relationship (1 function to n subfunctions), you could also just load the two tables (your index table and the master table, and link both using the index).

So an actually lookup will be performed during runtime dependent on the selection on this index field.

If you could post a small sample (just some lines) of your data and what the expected outcome is, I believe someone here in the forum is able to help you pretty fast.

Regards,

Stefan

Re: Lookup or matching function

Fri, 25 Nov 2011 19:22:25 GMT

I have attached two files.

What I want for qlik, is for it to look up the Ids in look.xlsx in master.xlsx, and return the value for each of f1,f2,f3,f4.

The original DB isn't contained in excel format and is about 100.000 lines, why a simple lookup function in excel is way to time consuming.

/Rune

Lookup or matching function

swuehl — Sat, 26 Nov 2011 16:25:29 GMT

I think you could just use something like

LOOK:

LOAD ID

FROM

Look.xlsx

(ooxml, embedded labels, table is Sheet1);

MASTER:

LOAD ID,

F1,

F2,

F3,

FROM

MASTER.xlsx

(ooxml, embedded labels, table is Sheet1) where Exists(ID);

or directly join the second load:

LOOK:

LOAD ID

FROM

Look.xlsx

(ooxml, embedded labels, table is Sheet1);

MASTER:

Left Join (LOOK) LOAD ID,

F1,

F2,

F3,

FROM

MASTER.xlsx

(ooxml, embedded labels, table is Sheet1) where Exists(ID);

Regards,

Stefan