Vlookup

[subbareddykm]

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: 

Emp	Date	City
100010	1/1/2016	1
100011	1/2/2016	2
100012	1/3/2016	3
100013	1/4/2016	4
100014	1/5/2016	5
100015	1/6/2016	6

Table 2:

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

Output:

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

Can any one tell me logic for this ?

[swuehl]

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_]

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]

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