Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

count of field values

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_idcount
1241
221
321
451
572
4762
23
771
141
233
171
4322

Please note this i need this data while loading only.

Thanks,

Pramod

Tags (1)
1 Solution

Accepted Solutions
pokassov
Valued Contributor

Re: count of field values

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;

11 Replies
pokassov
Valued Contributor

Re: count of field values

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;

Not applicable

Re: count of field values

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
Valued Contributor

Re: count of field values

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

Not applicable

Re: count of field values

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_idcount_user_idcount_correctcount_incorrect
131082
2510100
18000

This thing i need during load time.

Thanks,

Pramod

pokassov
Valued Contributor

Re: count of field values

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;

Not applicable

Re: count of field values

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
Valued Contributor

Re: count of field values

write path for your file instead "..."

Not applicable

Re: count of field values

i have written the path for that.

pokassov
Valued Contributor

Re: count of field values

I've used your file to check.

This script is ok:

LOAD distinct user_id

FROM

(ooxml, embedded labels, table is Sheet1);