Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
subbareddykm
Creator II
Creator II

Vlookup

Hi All,

I have one requirement ,

In that i have to create vlookup functionality .

I have One Table, by using conditions i m creating two diff tables.In that two tables i have to findout unmatched Emp_Id

Data  is Like :

Table 1: 

EmpDateCity
1000101/1/20161
1000111/2/20162
1000121/3/20163
1000131/4/20164
1000141/5/20165
1000151/6/20166

Table 2:

 

EmpDateCity
1000101/1/20161
1000111/2/20162
1000121/3/20163
1000171/4/20164
1000181/5/20165
1000191/6/20166
1000201/7/20167
1000211/8/20168
1000221/9/20169


Output:

 

1000171/4/20164
1000181/5/20165
1000191/6/20166
1000201/7/20167
1000211/8/20168
1000221/9/20169

Can any one tell me logic for this ?

3 Replies
swuehl
MVP
MVP

Try WHERE NOT EXISTS:

Exclude:

LOAD Emp

FROM Table1Source;

Result:

LOAD Emp, Date, City

FROM Table2Source

WHERE NOT EXISTS(Emp);

DROP TABLE Exclude;

Note that exists() checks against all values loaded so far, so you should not load Emp values before this small script in your load script sequence.

crusader_
Partner - Specialist
Partner - Specialist

Hi,

It's not a super graceful solution but you don't care about limitations of Exists function:

Add Exclude flag to first table:

Table1:

LOAD * INLINE [

Emp, Date, City, ExcludeFlag

100010, 1/1/2016, 1, 1

100011, 1/2/2016, 2, 1

100012, 1/3/2016, 3, 1

100013, 1/4/2016, 4, 1

100014, 1/5/2016, 5, 1

100015, 1/6/2016, 6, 1

];

Table2:

Right Join(Table1)

LOAD * INLINE [

Emp, Date, City

100010, 1/1/2016, 1

100011, 1/2/2016, 2

100012, 1/3/2016, 3

100017, 1/4/2016, 4

100018, 1/5/2016, 5

100019, 1/6/2016, 6

100020, 1/7/2016, 7

100021, 1/8/2016, 8

100022, 1/9/2016, 9

];

Result:

NoConcatenate

LOAD Emp

  , Date

  , City

Resident Table1 Where isnull(ExcludeFlag);

Drop Table Table1;

Hope this helps.

Kind regards,

Andrei

saikatghosh
Contributor III
Contributor III

Hi Sybbareddy,

you can try this

[Table 1]:
LOAD * Inline [
Emp Date
100010
100011
100012
100013
100014
100015
]
;

[Table 2]:
LOAD * INLINE [
Emp Date, City, F3
100010, 1/1/2016, 1
100011, 1/2/2016, 2
100012, 1/3/2016, 3
100017, 1/4/2016, 4
100018, 1/5/2016, 5
100019, 1/6/2016, 6
100020, 1/7/2016, 7
100021, 1/8/2016, 8
100022, 1/9/2016, 9
]

Where not Exists ([Emp Date]);

DROP Table [Table 1];

Regards

Saikat