Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Vlookup

Hello,

I have two tables, one with 2 columns and other with 3 coulmns. In second table 1 coulmn in merger of columns of first table. We want to combine the  two tables in a way that first table-two columns and second tables 2 two columns come together. The column which is merger of two columns of will not be in the output but the data maps correctly.

We have tried to do this with help of two join. However is there any other optimal solution ?

Please find attached example for same.

Thanks,

Nish

1 Solution

Accepted Solutions
Highlighted
er_mohit
Honored Contributor II

Re: Vlookup

Hi

Try this code in your application and reload it

Table1:

LOAD * Inline [

TID, SID

1, 10

2, 20

3, 30

];

Table2:

LOAD if(Exists(TID,FID),FID,'') as TID,

if(Exists(SID,FID),FID,'') as SID, * Inline [

FID, Country, Pin

1, abc, 100

2, efg, 100

10, def, 200

30, abc, 200

3, efg, 300

20, def, 300

];

DROP Table Table1;

DROP Field FID;

See attached file Also

10 Replies
MVP
MVP

Re: Vlookup

Read about Applymap() in qv.

deepaktibhe
Valued Contributor

Re: Vlookup

Hi,

You can chk applymap function along with mapping load in qlikview.

It works as lookup function in SQL.

Thanks,

Deepak

Not applicable

Re: Vlookup

Hello,

The applymap function does not give intended result.

Please can anyone suggest an alternative,

Regards,

Nish

deepaktibhe
Valued Contributor

Re: Vlookup

please tell ,what is your requirement

Thanks

Highlighted
er_mohit
Honored Contributor II

Re: Vlookup

Hi

Try this code in your application and reload it

Table1:

LOAD * Inline [

TID, SID

1, 10

2, 20

3, 30

];

Table2:

LOAD if(Exists(TID,FID),FID,'') as TID,

if(Exists(SID,FID),FID,'') as SID, * Inline [

FID, Country, Pin

1, abc, 100

2, efg, 100

10, def, 200

30, abc, 200

3, efg, 300

20, def, 300

];

DROP Table Table1;

DROP Field FID;

See attached file Also

Not applicable

Re: Vlookup

Hello Deepak,

I have given the requirment in the attached excel.

Regards,

Nisha

deepaktibhe
Valued Contributor

Re: Vlookup

Hi mohit,

Can you please tell me how

LOAD if(Exists(TID,FID),FID,'') as TID,

if(Exists(SID,FID),FID,'') as SID,

Will work as  you are not using any resident load here.

thanks,

martynlloyd
Contributor III

Re: Vlookup

My solution, paste into new doc to test, then simply change the residents to point at your tables:

// create test data

Table1:
LOAD * Inline [
TID, SID
1, 10
2, 20
3, 30
];

Table2:
LOAD * Inline [
FID, Country, Pin
1, abc, 100
2, efg, 100
10, def, 200
30, abc, 200
3, efg, 300
20, def, 300
];

// end test data


Output:
LOAD
TID as KEY,
TID
Resident Table1;

Concatenate
LOAD
SID as KEY,
SID
Resident Table1;

Left join (Output)
LOAD
FID as KEY,
Country,
Pin
Resident Table2;

Drop Tables Table1, Table2;

er_mohit
Honored Contributor II

Re: Vlookup

Hi

Read in help about exist function briefly

This function works with previous loaded  table field and on basis of that i match the FID value to call one by one as per requirement.

Here's definition

exists(field [ , expression ] )

Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field. If omitted, the current record’s value in the specified field will be assumed.

Examples:

exists(Month, 'Jan') returns -1 (true) if the field value 'Jan' is found in the current content of the field Month.

exists(IDnr, IDnr) returns -1 (true) if the value of the field IDnr in the current record already exists in any previously read record containing that field.

exists (IDnr) is identical to the previous example.

Load Employee, ID, Salary from Employees.csv;
Load FirstName& ' ' &LastName as Employee, Comment from Citizens.csv where exists (Employee, FirstName& ' ' &LastName);
Only comments regarding those citizens who are employees are read.

Load A, B, C, from Employees.csv where not exists (A);
This is equivalent to performing a distinct load on field A.