13 Replies Latest reply: Aug 4, 2015 5:09 AM by Rajesh V RSS

    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

      [C:\Users\1414571\Desktop\Productivity\MTS-UserList.xls]

      (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

      [C:\Users\1414571\Desktop\Productivity\SC STAR\*.txt]

      (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

      [C:\Users\1414571\Desktop\Productivity\MTS - Japan\*.xlsx]

      (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

      [C:\Users\1414571\Desktop\Productivity\MTS - FFT\*.xlsx]

      (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