Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have attached a 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 where 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
I think you should simply do this with a table object and not in the load script:
Create a table object with:
Dimension: user_id
Measures:
count(user_id)
count({<is_correct_answer={1}>} user_id)
count({<is_correct_answer={0}>} user_id)
But really if you want to:
MyData:
LOAD *, count_of_userid - count_correct as count_incorrect;
LOAD
user_id,
count(user_id) as count_of_userid,
sum(is_answer_correct) as count_correct,
FROM ....
WHERE test_type = 'FinalTest'
GROUP BY user_id;
Hi Gysbert Wassenaar,
I really need to load the data.u have answered one of my another post
for that table i need to add these fields that's y i need it as a field while loading.
the above code not working
would you please help on that.
Thanks,
Pramod
Hi,
Try this script
Data:
LOAD user_id,
is_correct_answer,
test_type,
If(test_type = 'FinalTest', 1, 0) AS UserCount,
If(is_correct_answer = 1, 1, 0) AS count_correct,
If(is_correct_answer = 0, 1, 0) AS count_not_correct
FROM
[correct.csv.xlsx]
(ooxml, embedded labels, table is Sheet1);
Now in expression just use
Sum(UserCount)
Sum(count_correct)
Sum(count_not_correct)
Hope this helps you
Regards,
Jagan.
Hi jagan mohan,
you didn't got my point.
Actually i need it as field names.
user_id | count_user_id | count_correct | count_incorrect |
---|---|---|---|
25 | 10 | 10 | 0 |
18 | 0 | 0 | 0 |
13 | 10 | 8 | 2 |
This table view is what i need in "data model viewer" not in sheets.
Thanks,
Pramod
HI gysbert wassenaar,
I am using below code to load the table but it's getting error.
LIB CONNECT TO 'MyDB';
LOAD user_id,
is_correct_answer,
sum(is_correct_answer) as count_correct,
count(user_id) as count_of_userid,
count_of_userid - count_correct as count_incorrect;
SQL SELECT `user_id`,
is_correct_answer
FROM rcdbrpt.userquestion GROUP BY user_id;
The error is,
The following error occurred:
Field not found - <count_of_userid>
The error occurred here:
SQL SELECT `user_id`, is_correct_answer FROM rcdbrpt.userquestion GROUP BY user_id
Would you please help. if you have doubt about question please ask.
Thanks,
Pramod
Try:
LIB CONNECT TO 'MyDB';
LOAD * , count_of_userid - count_correct as count_incorrect;
SQL SELECT user_id,
sum(is_correct_answer) as count_correct,
count(user_id) as count_of_userid
FROM rcdbrpt.userquestion
WHERE test_type = 'FinalTest'
GROUP BY user_id;
Hi Gysbert wassenar,
I have encountered one issue please tell me what's the problem for this.
in filter pane if i select any value it's showing different value.
I have attached the screenshot.
i have selected the 202 but it is displaying the value of 467. what may be the problem.
Please help on it.
Thanks,
Pramod
The id's must be loaded in ascending order. Id 1 must be the first record, id 2 the second, and so on.
The data what loaded is correct. but while i selecting the id in filterpane. it's selecting the value of different id.
LIB CONNECT TO 'MyDB';
LOAD * , count_of_userid - count_correct as count_incorrect;
SQL SELECT user_id,
sum(is_correct_answer) as count_correct,
count(user_id) as count_of_userid
FROM rcdbrpt.userquestion
WHERE test_type = 'FinalTest'
GROUP BY user_id;
one more thing is eventhough we have given "WHERE test_type = 'FinalTest' " it is selecting all other values also.
Please help.