Create lookup table from two tables

[berryandcherry6]

Hi,

I have a requirement where i need to create lookup table like below shown, here all datas will be dynamic

Table1:

fruit_id    fruit_type                 fruit_selled

1             stocked                       3

2             New_type                   4

3             Middle_arrived            4

4             Middle_arrived            1

5             New_type                   2

6             New_type                   3

7             stocked                       4

8             stocked                       5

Table2:

seller_id seller_name   fruit_id   fruit_type

45            gaurav           2             New_type    

46            timer              2             Middle_arrived

47             sita                3            Middle_arrived

38            veena             4            New_type

39             matias           4            stocked                                  

23             joshi              6            New_type                  

24             vital                8           stocked                      

From above 2 tables i want to create a lookup table like below

LookupTable:

seller_id seller_name   fruit_id   fruit_type               fruit_selled

45            gaurav           2             New_type                  4

46            timer              2             Middle_arrived           0

47             sita                3            Middle_arrived           4

38            veena             4            New_type                  0

39             matias           4            stocked                      0            

23             joshi              6            New_type                  3

24             vital                8           stocked                      5

How could i do this? Please help me on this

[kaushiknsolanki · Accepted solution]

Try this.

Map:

Mapping load fruit_id &fruit_type as FruitLookup,  fruit_selled From table1;

Table2:

Load *,appymap('Map', fruit_id &fruit_type)  as Fruit_Selled From table2;

Regards,

Kaushik Solanki

[berryandcherry6]

Thanks Kaushik, it worked

[berryandcherry6]

Hi Kaushik,

in Table1, there are duplicates record with different fruit_selled counts, So now i need to find sum of this records, For this i used below code

Table1:

Load * Inline [

fruit_id ,   fruit_type  ,               fruit_selled

1 ,            stocked       ,                3

2 ,            New_type       ,            4

3 ,           Middle_arrived  ,          4

3 ,           Middle_arrived  ,          1

3 ,           Middle_arrived  ,          6

4  ,           Middle_arrived ,           1

4  ,           Middle_arrived ,           4

5  ,           New_type       ,            2

6  ,           New_type       ,            3

7  ,           stocked        ,               4

8  ,           stocked        ,               5];

Table2:

Load * Inline [

seller_id, seller_name,   fruit_id,   fruit_type

45 ,           gaurav,           2    ,         New_type   

46 ,           timer ,             2    ,         Middle_arrived

47 ,            sita ,               3     ,       Middle_arrived

38  ,          veena ,            4       ,     New_type

39 ,            matias,           4      ,      stocked                                 

23 ,            joshi ,             6     ,       New_type                 

24 ,            vital ,               8   ,        stocked    ];   

Map:

Mapping load fruit_id &fruit_type as FruitLookup,  sum(fruit_selled) Resident Table1 Group By fruit_id ,fruit_type ;

Table3:

Load *,ApplyMap('Map', fruit_id &fruit_type)  as Fruit_Selled Resident Table2;

drop Table Table1, Table2;

But this gives me wrong result, How can i get sum fruit_selled

[mdmukramali]

Dear Supriya,

I think groupby will not work in Mapping load.

here the updated code:

try it.

Table1:
Load * Inline [
fruit_id,fruit_type,fruit_selled
1,stocked,3
2,New_type,4
3,Middle_arrived,4
3,Middle_arrived,1
3,Middle_arrived,6
4,Middle_arrived,1
4,Middle_arrived,4
5,New_type,2
6,New_type,3
7,stocked,4
8,stocked,5
]
;

TEMP_MAP:
Mapping
LOAD
fruit_id&'-'&fruit_type AS FruitLookup,
sum(fruit_selled) as FRUIT_SELLED
Resident Table1
Group BY fruit_id,fruit_type;
DROP Table Table1;

//MAP_TABLE:
//Mapping
//LOAD FruitLookup,
//FRUIT_SELLED
//Resident TEMP;
//DROP Table TEMP;

Table2:
Load * Inline [
seller_id,seller_name,fruit_id,fruit_type
45,gaurav,2,New_type
46,timer,2,Middle_arrived
47,sita,3,Middle_arrived
38,veena,4,New_type
39,matias,4,stocked       
23,joshi,6,New_type
24,vital,8,stocked
];

//NoConcatenate
//LOAD *,
//fruit_id&'-'&fruit_type AS FruitLookup
//Resident Table2;
//DROP Table Table2;   
///*
Load *,ApplyMap('TEMP_MAP',fruit_id&'-'&fruit_type,'') as FRUIT_SELLED Resident Table2;
drop Table Table2;

I hope it will help you,

Thanks,

Mukram.