topic Re: Create lookup table from two tables in QlikView

Create lookup table from two tables

berryandcherry6 — Sun, 13 Aug 2017 09:22:55 GMT

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

Re: Create lookup table from two tables

kaushiknsolanki — Sun, 13 Aug 2017 09:48:39 GMT

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

Re: Create lookup table from two tables

berryandcherry6 — Sun, 13 Aug 2017 10:11:56 GMT

Thanks Kaushik, it worked

Re: Create lookup table from two tables

berryandcherry6 — Wed, 16 Aug 2017 12:10:10 GMT

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

Re: Create lookup table from two tables

mdmukramali — Wed, 16 Aug 2017 12:45:49 GMT

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.