Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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.

View solution in original post

7 Replies
swuehl
MVP
MVP

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)

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.


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

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.

Gysbert_Wassenaar

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
swuehl
MVP
MVP

No worries 😉

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

Not applicable
Author

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

swuehl
MVP
MVP

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.