data load from table

[#1]

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

[Gysbert_Wassenaar]

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;

[#3]

Hi Gysbert Wassenaar,

I really need to load the data.u have answered one of my another post

Re: Table Creation

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

[jagan]

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.

[#5]

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

[#6]

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

[Gysbert_Wassenaar]

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;

[#8]

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

[Gysbert_Wassenaar]

The id's must be loaded in ascending order. Id 1 must be the first record, id 2 the second, and so on.

[#10]

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.