Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
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
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