count of field values

[#1]

hi all,

I have a table with two columns user_id and percentage_score in results table. I have attached the sample csv file.

while loading the data i need to load user_id and count of each user_id.

for example: user_id = 476 i have two times in table so i need count as 2.

After loading table should look like below:

User_id	count
124	1
22	1
32	1
45	1
57	2
476	2
2	3
77	1
14	1
23	3
17	1
432	2

Please note this i need this data while loading only.

Thanks,

Pramod

[pokassov · Accepted solution]

Hello!

If you have to count User_id, you could use count(User_id) as expression.

If you need this during the load you can use something follow:

your_table:

load ....;

t1:

load User_id,

     count (User_id) as cnt

Resident your_table

group by User_id;

left join (your_table)

load * resident t1;

drop table t1;

[#3]

hi,

If we give count(User_id) as expression, i will get complete count of user_id which is in the table. not the count of particular user_id.

[pokassov]

If you won't use User_id as dimension - yes.

[#5]

hi Сергей Покасов,

The above code worked fine thanks a lot.

I have attached another csv file.

where i need to load the data from the attached table.

For example:

column 1 : User_id

column 2: In the attached table i have user_id = 13 where i need the count of user_id = 13 whose test_type = FinalTest.

column 3: count of user_id = 13 where is_correct_ans = 1;

column 4: count of user_id = 13 where is_correct_ans = 0;

table must look like below,

user_id	count_user_id	count_correct	count_incorrect
13	10	8	2
25	10	10	0
18	0	0	0

This thing i need during load time.

Thanks,

Pramod

[pokassov]

table1:

load distinct user_id

from ...;

t1:

load

user_id

count(user_id) as count_user_id

from ...

where test_type='FinalTest';

left join (table1)

load * resident t1; drop table t1;

t1:

load

user_id

count(user_id) as count_correct

from ...

where is_correct_ans=1;

left join (table1)

load * resident t1; drop table t1;

t1:

load

user_id

count(user_id) as count_incorrect

from ...

where is_correct_ans=0;

left join (table1)

load * resident t1; drop table t1;

[#7]

Hi,

Tried above code getting error like below

The following error occurred:

LOAD statement only works with lib:// paths in this script mode

The error occurred here:

table1: load distinct user_id from usertable

[pokassov]

write path for your file instead "..."

[#9]

i have written the path for that.

[pokassov]

I've used your file to check.

This script is ok:

LOAD distinct user_id

FROM

(ooxml, embedded labels, table is Sheet1);