7 Replies Latest reply: Nov 1, 2013 11:04 AM by Stefan Wühl RSS

    Creating a table with Manual Text Rows and Calculated Field Rows

      Hi,

       

      I am trying to create a Table which on the the left has Manual Text and on the right a Calculated Field:  See Below:

       

      Manaul TextCalculated Field
      Last Updated more than 3 days ago  =count(distinct if(time(update_time_JD_1,'hh,mm,ss') > time('30:00:00','hh,mm,ss') AND time(update_time_JD_1,'hh,mm,ss') < time('49:59:59','hh,mm,ss'), update_time_JD_1))

      Last Updated more than 5 days ago

        =count(distinct if(time(update_time_JD_1,'hh,mm,ss') > time('50:00:00','hh,mm,ss') AND time(update_time_JD_1,'hh,mm,ss') < time('99:59:59','hh,mm,ss'), update_time_JD_1))
      Last Updated more than 10 days ago  =count(distinct if(time(update_time_JD_1,'hh,mm,ss') > time('100:00:00','hh,mm,ss') AND time(update_time_JD_1,'hh,mm,ss') < time('139:59:59','hh,mm,ss'), update_time_JD_1))

       

      What I am hoping to achieve is the table below:

       

      Manual TextCalculated Field
      Last Updated more than 3 Days ago2
      Last Updated mor ethan 5 Days ago9
      Last Updated more than 10 Days ago1

       

      What I also want to go is for the user to click on the calculated field number and it pulls up in different table the reference numbers where the update_time_JD_1 field is for exmpale Less than 3 Days (in hours).  Can this be done?

       

      Regards,

       

      Jon Ditchfield

        • Re: Creating a table with Manual Text Rows and Calculated Field Rows
          Stefan Wühl

          Try a calculated dimension (or a new field in your data model with the same logic):

           

          =aggr(

          if(time(update_time_JD_1,'hh,mm,ss') > time('30:00:00','hh,mm,ss') AND time(update_time_JD_1,'hh,mm,ss') < time('49:59:59','hh,mm,ss'),

          'Last Updated more than 3 days ago',

          if(time(update_time_JD_1,'hh,mm,ss') > time('50:00:00','hh,mm,ss') AND time(update_time_JD_1,'hh,mm,ss') < time('99:59:59','hh,mm,ss'),

          'Last Updated more than 5 days ago','Last Updated more than 10 days ago'))

          update_time_JD_1)

           

          (double check the opening and closing brackets, and I think you should use interval() instead of time() here)

          and as expression

           

          =count(distinct update_time_JD_1)

          • Re: Creating a table with Manual Text Rows and Calculated Field Rows
            Gysbert Wassenaar

            I'd create the field in the script:

             

            load x,y,z,

            if(update_time_JD_1>=30/24 and update_time_JD_1 < 50/24,'Last Updated more than 3 days ago',

            if(update_time_JD_1>=50/24 and update_time_JD_1 < 100/24,'Last Updated more than 5 days ago',

            if(update_time_JD_1>=100/24 and update_time_JD_1 < 140/24,'Last Updated more than 10 days ago'))) as LastUpdated

            from ...mysource...;

             

            If you click on a value of an expression then the dimension values of that row are selected. So that's not going to work. However if you create the field LastUpdated in the script users can select a value in that field and you can show the related details in a separate table.

            • Re: Creating a table with Manual Text Rows and Calculated Field Rows

              pHi All,

               

              I tried your replies but Im still having issues.  I think the issue I have is the way I have built my load script.  Would you mind having a look at the load script and seeing where I can put your answers as when I enter it in the load script I keep getting a message saying: update_time_JD_1 does not exist.

               

              Attached is a copy of my load script.

               

              Regards,

               

              Jon Ditchfield

                • Re: Creating a table with Manual Text Rows and Calculated Field Rows
                  Stefan Wühl

                  Maybe just add a preceding load to the LOGINFO table load:

                   

                  LOGINFO:

                  LOAD *,

                  if(update_time_JD_1>=30/24 and update_time_JD_1 < 50/24,'Last Updated more than 3 days ago',

                  if(update_time_JD_1>=50/24 and update_time_JD_1 < 100/24,'Last Updated more than 5 days ago',

                  if(update_time_JD_1>=100/24 and update_time_JD_1 < 140/24,'Last Updated more than 10 days ago'))) as LastUpdated;

                   

                  LOAD *, Month([logdate]) as Logdate_Month,

                    Year([logdate]) as Logdate_Year,

                    day([logdate]) as Logdate_day, 

                    Month([closedate]) as closedate_Month,

                    Year([closedate]) as closedate_Year,

                    day([closedate]) as closedate_day, 

                    date([logdate]) as Logdate_JD

                    ,num(today()) - 1460 as test123

                    ,num([logdate]) as logdatetest

                    ,ConvertToLocalTime (logdate) as logdate_true

                    ,ConvertToLocalTime (closedate) as closedate_true

                    ,ConvertToLocalTime (respondby) as respondby_true

                    ,interval(time(fix_time/86400, 'hh:mm:ss')) as fix_time_true

                    ,interval(time(resp_time/86400, 'hh:mm:ss')) as resp_time_true

                    ,if(isnull(withinresp),'N/A',

                    if(withinresp = 1, 'Yes', 'No')) as within_resp_true

                    ,if(isnull(withinfix),'N/A',

                    if(withinfix = 1, 'Yes', 'No')) as within_fix_true

                   

                    ,if(call_status_full = 'Pending' or call_status_full = 'Unaccepted' or call_status_full = 'Off Hold' or call_status_full = 'Unassigned', 

                           Interval((RangeMin(frac(now()), MakeTime(18))- RangeMax(frac(lastactdate), MakeTime(8)))+ (NetWorkDays(lastactdate, now()-1) * MakeTime(10)) // Only 10 hours per whole day

                    )) as update_time_JD_1

                    

                   

                    where num(today()) - 1460 < [logdate];

                   

                  I noticed that you labelled some preceding load sections (Opencall, Probcode_1, Callstatus_1) I don't think this works.