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

Creating a table with Manual Text Rows and Calculated Field Rows

Wed, 30 Oct 2013 15:25:24 GMT

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 Text	Calculated 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 Text	Calculated Field
Last Updated more than 3 Days ago	2
Last Updated mor ethan 5 Days ago	9
Last Updated more than 10 Days ago	1

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

swuehl — Wed, 30 Oct 2013 15:54:14 GMT

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 — Wed, 30 Oct 2013 16:01:00 GMT

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

swuehl — Wed, 30 Oct 2013 16:06:49 GMT

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

Gysbert_Wassenaar — Wed, 30 Oct 2013 16:12:04 GMT

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.

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

swuehl — Wed, 30 Oct 2013 16:20:38 GMT

No worries 😉

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

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

Fri, 01 Nov 2013 14:42:44 GMT

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

swuehl — Fri, 01 Nov 2013 15:04:45 GMT

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.