Lookup or matching function

[#1]

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

[swuehl]

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

[#3]

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

[swuehl]

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,

     F4

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,

     F4

FROM

MASTER.xlsx

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

Regards,

Stefan