Resident Syntax

[#1]

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

[Anonymous]

hi,

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

Regards

Neetha

[jonathandienst]

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.

[Gabriel]

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

[#5]

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

[Anonymous]

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

[#7]

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

[Anonymous]

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

[#9]

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

[#10]

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