Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Just try in teh QVW which i have attached to see if it works at your end
Hi,
Please be specific what you want to achieve.
so can help
Use Aggr function in expression in UI to group by ID
I wanted to get output like this.
Location | Average | 0-400 | 400-800 | 800-1000 | 1000-1500 |
Bangalore | 10 | 6 | 4 | 1 | |
Haddows | 15 | 6 | - | - | |
Haddows - TCS | 8 | - | - | - | |
Kenya | 3 | 1 | - | - | |
KL | 3 | 1 | - | - | |
Padur | 1 | 2 | 2 | - | |
Tianjin | 13 | 3 | - | - |