Help need in creating an dimension in script level

[vinod22kv]

Hi,

i am trying to create the dimension like 3 column.

If name = vinod and if id has repetied then count of that id's. I want this in script level.

Name	id	vinod
vinod	1	2
vinod	1	2
vinod	2	3
vinod	2	3
vinod	2	3
Siva	3	0
Siva	4	0
Siva	4	0
Gopi	5	0

Thanks and Regards,

Vinod.

[zhadrakas]

not the best solution. But it should work for you:

TEST:
LOAD * INLINE [
Name, id
vinod, 1
vinod, 1
vinod, 2
vinod, 2
vinod, 2
vinod, 3
Siva, 3
Siva, 4
Siva, 4
Gopi, 5
];

left join
Load Name,
id,
count(if(Name='vinod', id)) as count_id
Resident TEST
GROUP BY Name, id
;

left join
Load Name,
id,
count(if(Name='vinod' and count_id>1, id)) as vinod
Resident TEST
GROUP BY Name, id
;

[Hariharan_S · Accepted solution]

Hi

Try this code.

table1:
load * Inline
[name,id
vinod,1
vinod,1
vinod,2
vinod,2
vinod,2
siva,3
siva,4
siva,4
gopi,5];
Map_Tab:
Mapping
Load Distinct name,count(name) as NameCount
resident table1 group by name;
Final_Table:
Load *,Applymap('Map_Tab',name,'No Count') as NameCount
resident table1;
Drop table table1;