10 Replies Latest reply: May 25, 2015 7:21 AM by Kumar Pramod RSS

    data load from table

    Kumar Pramod

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

       

       

       

      This thing i need during load time.

       

       

      Thanks,

      Pramod

        • Re: data load from table
          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;

            • Re: data load from table
              Kumar Pramod

              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

                • Re: data load from table
                  jagan mohan rao appala

                  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.

                • Re: data load from table
                  Kumar 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

                    • Re: data load from table
                      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;

                    • Re: data load from table
                      Kumar Pramod

                      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.

                       

                      data.png

                      i have selected the 202 but it is displaying the value of 467. what may be the problem.

                       

                      Please help on it.

                       

                      Thanks,

                      Pramod