Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

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

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.

7 Replies
MVP
MVP

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

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

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.


talk is cheap, supply exceeds demand
MVP
MVP

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

Yes, I also suggested that one can add the field to the script if the classification is static (as it seems in this example).

But why shouldn't above work in principle?

If you select a dimension value in the table, I believe the selection will basically be done on the aggr() dimensions values involved, i.e. selection will be done on update_time_JD_1, which seems just what is needed here.

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

Oh, I hadn't seen your response yet. No arguments from me regarding your first response. I wouldn't dare :-). My post was a reply to Jonathan, not to your post. Selecting a value in the calculated dimension works fine afaik. It's just that selecting a value in the expression won't work. I understood 'calculated field number' to mean a result value of the expression.


talk is cheap, supply exceeds demand
MVP
MVP

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

No worries ;-)

Your answer seems to match two posts at once, kudos (or krediet?)!

Not applicable

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

MVP
MVP

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

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.

Community Browser