Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Resident Syntax

Hi All,

IN the below Syntax i am using Resident Table for Grouping.

And if you look at the Productivity Table & Resident Table New ,  ID is used as Key.Iinstead of it I need to use the actual Key field  in Productivity table as Key for both the tables so that when i select date the average Bucket is calculated for the selected dates .In the below script irrespective of date selected the average is calculated for all dates.

MTS:

MAPPING LOAD [Opr Initials],

ID

FROM

(biff, embedded labels, table is [Sheet2$]);

Tab:

LOAD [@1:4] as Interval1,

     [@5:12] as ID1,

     [@13:18] as [Dept ID],

     [@19:30] as Status,

     [@31:41] as Handled,

     [@42:n] as Closed,

  Subfield(FileName(),'.',1) AS Date_Test

FROM

(fix, codepage is 1252);

NoConcatenate

Productivity:

LOAD Date(Date#(Date_Test,'YYYY-MM-DD'),'YYYY-MM-DD') As Date,

  if(len(trim(Interval1))=0,peek('Interval'),Interval1) AS Interval,

  if(len(trim(ID1))=0,peek('ID'),ID1) AS ID,

  'SCSTAR' as Source,

if(len(trim(ID1))=0,peek('ID'),ID1) & Date(Date#(Date_Test,'YYYY-MM-DD'),'YYYY-MM-DD')  as Key,

  *

  Resident Tab Where len(Trim([Dept ID]))>0;

DROP Table Tab;

DROP Fields Interval1,ID1,Date_Test;

Concatenate

LOAD Date,

     [Opr Initials],   

     ApplyMap('MTS',[Opr Initials]) as ID,

     OFP,

     OFA,

     OFP+OFA as Handled,

     'JPN' as [Dept ID],

     'MTS-JPN' as Source,

    ApplyMap('MTS',[Opr Initials]) &  Date as Key,

     Memo

FROM

(ooxml, embedded labels, table is List_Frame_1);

Concatenate

LOAD [Maker BankID] as ID,

     Date(Date#([Date (DD/MM/YYYY)],'DD/MM/YYYY'),'YYYY-MM-DD')as Date,

     Volume as Handled,

     'FFT' as [Dept ID],

      'MTS-FFT' as Source,

[Maker BankID] & Date(Date#([Date (DD/MM/YYYY)],'DD/MM/YYYY'),'YYYY-MM-DD')as Key,

     [Role (50)]

FROM

(ooxml, embedded labels, table is Sheet1);

New:

LOAD

ID,

//Sum(Handled)/Count(Distinct(Date)) as AvgHandled,

if(Sum(Handled)/Count(Distinct(Date)) < 400, '0-400',

  if(Sum(Handled)/Count(Distinct(Date)) < 800, '400-800',

  if(Sum(Handled)/Count(Distinct(Date)) < 1000, '800-1000',

  if(Sum(Handled)/Count(Distinct(Date)) < 1500, '1000-1500', '> 1500')))) as Average

  Resident Productivity

Group by ID;

How do i link dates between both Productivity & New table

Attaching teh QVW

Tags (1)
13 Replies
neetha_p
Honored Contributor

Re: Resident Syntax

hi,

use interval function for creating buckets for calculating avg  for specific date ranges.

Regards

Neetha

MVP
MVP

Re: Resident Syntax

Because the table (New) with the average values is associated only by ID, so the average will be for possible values ID, regardless of date.

If you want user selectable date ranges, then the Average should be calculated in the front end expressions and not in the script.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Gabriel
Valued Contributor II

Re: Resident Syntax

Hi,

Am not sure I fully understand your point. But I think you can bring in the Date field from Productivity Table since you're using Resident to create the New table.

So just add Date field into the New table and that should work

Not applicable

Re: Resident Syntax

I am really new to Qlikview.Can you help me out in writing teh syntax

neetha_p
Honored Contributor

Re: Resident Syntax

please can you give requirements (date ranges) to be calculated for avg in script

Not applicable

Re: Resident Syntax

If i write it this way

New:

LOAD

ID , Date,

if(Sum(Handled)/Count(Distinct(Date)) < 400, '0-400',

  if(Sum(Handled)/Count(Distinct(Date)) < 800, '400-800',

  if(Sum(Handled)/Count(Distinct(Date)) < 1000, '800-1000',

  if(Sum(Handled)/Count(Distinct(Date)) < 1500, '1000-1500', '> 1500')))) as Average

  Resident Productivity

  

  Group BY ID;

It says Invalid exopression and more over it will create Sysnthetic Key as i have both ID & Date so if change ID as ID1 then group by Doesnot work .

And if i change Group by ID1; then it throws as error saying Field ID1 doesnot exist

neetha_p
Honored Contributor

Re: Resident Syntax

Include date in group by or exclude date in load statement for group by

Not applicable

Re: Resident Syntax

I am able to get average outside scrip in graph but if i want to bucklt each agent under a varios bucklets to see the locationwise Count under each bucket then i need to do it Script only right.Is

Not applicable

Re: Resident Syntax

i cannot add date in group by as I wanted the Average for the entire period and if i add date in group it stars calculating teh avg for each dates and creates bucket so finally one officer will have many buckets

Community Browser