1 Reply Latest reply: Dec 1, 2013 1:11 PM by Srikanth P RSS

    creating a table with a "grouped by" dimension

      Hi All,

      My problem is this:

       

      I need to create a table with 2 dimensions, when one of the dimensions need to be created by using "Group By" (meaning, need 2 other dimensions in itself in order to be calculated).

       

      I thought about creating and calculating the first "grouped by" dimension by the script, and then using that field as a dimension in order to create my desired table.

       

      I'm using the following in my script:

       

      AppClicks:

      Load

      app_id_click as app_id,

      text(Date(rec_date_click)) AS daily_click_date,

      count( Distinct clickviewAction) as App_daily_Clicks

      from  D:\qv\qvd_files\Detailed\Installs_usr_action_click.qvd(qvd)

      Group by (date(rec_date_click)), app_id_click;

       

      the Script loads fine with no issues, but the results I'm getting are somehow incorrect...

       

      Can you please:

      A. Point out if you notice anything wrong with the script

      B. Tell me if you know of an easier way of creating my histogram?

       

      Thanks

      Elula

        • Re: creating a table with a "grouped by" dimension
          Srikanth P

          I suspect your rec_date_click is Timestamp. So please use Floor function to truncate the time and like below.

           

          In the UI design please use required data format

           

          AppClicks:

          Load

          app_id_click as app_id,

          Floor(rec_date_click) AS daily_click_date,

          count( Distinct clickviewAction) as App_daily_Clicks

          from  D:\qv\qvd_files\Detailed\Installs_usr_action_click.qvd(qvd)

          Group by (Floor(rec_date_click)), app_id_click;