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

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

13 Replies
Not applicable
Author

Just try in teh QVW which i have attached to see if it works at your end

Anonymous
Not applicable
Author

Hi,

Please be specific what you want to achieve.

so can help

Anonymous
Not applicable
Author

Use Aggr function  in expression in UI to group by ID

Not applicable
Author

I wanted to get output like this.

 

LocationAverage0-400400-800800-10001000-1500
Bangalore 10641
Haddows 156--
Haddows - TCS 8---
Kenya 31--
KL 31--
Padur 122-
Tianjin 133--