7 Replies Latest reply: Sep 26, 2011 9:57 AM by Anand Chouhan RSS

    partial reload

      Hello,

       

      Please help. I'm not sure how to use partial reload. Replace/Add syntax.

       

      I have a lot of scripts but I need to reload only the pnce part.

       

      Ex.:

       

      //***Cover
      $(Include=\\10.248.1.15\operations reports\quality department\qlikview\qlikview scripts\production metrics\pm - load - cover.txt)

       

      ///***Slides
      $(Include=\\10.248.1.15\operations reports\quality department\qlikview\qlikview scripts\production metrics\pm - slides.txt)

       

      // //*** Headcount
      $(Include=\\10.248.1.15\operations reports\quality department\qlikview\qlikview scripts\tests\tester\tester - pm - load - Headcount.txt)

       

      // //*** Metric Targets
      $(Include=\\10.248.1.15\operations reports\quality department\qlikview\qlikview scripts\tests\tester\tester - pm - load - metric targets.txt)

       

      //*** Meeting Details
      $(Include=\\10.248.1.15\operations reports\quality department\qlikview\qlikview scripts\tests\tester\tester - pm - load - meeting details.txt)

       

      here's the script for Headcount:

       

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';


      LET CacheYear = YEAR(NOW());
      LET CacheYearMonth = '$(CacheYear)'&IF(TEXTBETWEEN(DATE(NOW()),'','/',1)<10,'0'&TEXTBETWEEN(DATE(NOW()),'','/',1),TEXTBETWEEN(DATE(NOW()),'','/',1));
      LET CacheDate = '$(CacheYearMonth)'&IF(TEXTBETWEEN(DATE(NOW()),'/','/',1)<10,'0'&TEXTBETWEEN(DATE(NOW()),'/','/',1),TEXTBETWEEN(DATE(NOW()),'/','/',1));

      LET ReloadStart = IF('$(RA)' = '','$(CacheDate)','$(CacheDate)'&' '&'$(RA)');

      LET CacheAdd = '\\10.248.1.15\Operations Reports\Quality Department\QliKView\QVDs\Cache\$(CacheYear)\$(CacheYearMonth)\$(CacheDate)\';

      [HC Teams]:
      NOCONCATENATE LOAD TeamID,
           TeamParent,
           [Team Name],
           [Team Short Name],
           [HC Folder],
           [HC Workbook],
           Extension1
      FROM
      [\\10.248.1.15\Operations Reports\Quality Department\QlikView\Reference Data\Teams.xls]
      (biff, embedded labels, table is Units$)
      WHERE([Team Short Name] = '$(TeamShort)');

      LET TeamName=PEEK('Team Name',0,'HC Teams');
      LET TeamShort=PEEK('Team Short Name',0,'HC Teams');
      LET TeamIDNum =PEEK('TeamID',0,'HC Teams');
      LET Folder =PEEK('HC Folder',0,'HC Teams');
      LET Book =PEEK('HC Workbook',0,'HC Teams');
      LET Extra =PEEK('Extension1',0,'HC Teams');

      LET QVDNum = '\\10.248.1.15\Operations Reports\Quality Department\QlikView\QVDs\Headcount Forecast\HC Num $(TeamShort).qvd';
      LET QVDCat = '\\10.248.1.15\Operations Reports\Quality Department\QlikView\QVDs\Headcount Forecast\HC Cat $(TeamShort).qvd';

      LET Source = '\\10.248.1.15\Operations Reports\Quality Department\Headcount Forecast\HC Forecast $(Folder)\HC Forecast - $(Book).xls';
      LET Source1 = '\\10.248.1.15\Operations Reports\MBPS $(Extra)\Headcount Forecast\HC Forecast $(Folder)\HC Forecast - $(Book).xls';

       

      LET QVDSave = TIMESTAMP#(FILETIME('$(QVDNum)'));
      LET SourceSave = TIMESTAMP#(FILETIME('$(Source)'));

      LET Disc = DIV(MONTHEND(TODAY(),-8),1);       /*** No Changes allowed.****/

      UNQUALIFY *;
      [HC Table]:
      NOCONCATENATE LOAD *
      FROM
      [$(QVDNum)]
      (qvd)
      WHERE(MetricIndex < '$(Disc)');
        
      CONCATENATE LOAD *,
        '$(TeamIDNum)' as TeamID,
        'Manila' as [HC Location]

       

      FROM
      [$(Source)]
      (biff, embedded labels, table is [$(TeamShort)$], filters(
      Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
      Transpose()
      ))
      WHERE(NOT ISNULL(MetricIndex)
      AND ISNUM(MetricIndex)
      AND ((MetricIndex > '$(Disc)')
      OR (MetricIndex = '$(Disc)')));

      IF '$(Extra)' = '' THEN
      ELSE
        CONCATENATE LOAD *,
         '$(TeamIDNum)' as TeamID,
         'Chengdu' as [HC Location]
        FROM
        [$(Source1)]
        (biff, embedded labels, table is [$(TeamShort)$], filters(
        Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
        Transpose()
        ))
        WHERE(NOT ISNULL(MetricIndex)
        AND ISNUM(MetricIndex)
        AND ((MetricIndex > '$(Disc)')
        OR (MetricIndex = '$(Disc)')));

       

      END IF


      STORE [HC Table] INTO '$(QVDNum)';
      STORE [HC Table] INTO '$(CacheAdd)$(ReloadStart) HC Num $(TeamShort).qvd';
      DROP TABLE [HC Table];
         
      [HC Categories]:
      NOCONCATENATE LOAD *,
        '$(TeamIDNum)' as TeamID,
        'Manila' as [HC Location]
        FROM
      [$(Source)]
      (biff, embedded labels, table is [$(TeamShort)$], filters(
      Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
      Transpose()
      ))
      WHERE((MetricIndex = 'JobCat')
      OR (MetricIndex = 'BillCode')
      OR (MetricIndex = 'Shift')
      OR (MetricIndex = 'FTE')) ;

      IF '$(Extra)' = '' THEN
      ELSE
        CONCATENATE LOAD *,
         '$(TeamIDNum)' as TeamID,
         'Chengdu' as [HC Location]
         FROM
        [$(Source1)]
        (biff, embedded labels, table is [$(TeamShort)$], filters(
        Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),
        Transpose()
        ))
        WHERE((MetricIndex = 'JobCat')
        OR (MetricIndex = 'BillCode')
        OR (MetricIndex = 'Shift')
        OR (MetricIndex = 'FTE')) ;

       

      END IF
        
      STORE [HC Categories] INTO '$(QVDCat)';
      STORE [HC Categories] INTO '$(CacheAdd)$(ReloadStart) HC Cat $(TeamShort).qvd';  
      DROP TABLE [HC Categories];

      [Headcount Table]:
      NOCONCATENATE  LOAD *
      FROM
      [$(QVDNum)]
      (qvd);

      [Headcount Category]:
      NOCONCATENATE LOAD *
      FROM
      [$(QVDCat)]
      (qvd);

      [Job Category]:
      NOCONCATENATE LOAD *
      RESIDENT [Headcount Category]
      WHERE(MetricIndex = 'JobCat');

      [Billing Code]:
      NOCONCATENATE LOAD *
      RESIDENT [Headcount Category]
      WHERE(MetricIndex = 'BillCode');

      [FTE Percentage]:
      NOCONCATENATE LOAD *
      RESIDENT [Headcount Category]
      WHERE(MetricIndex = 'FTE');

      [HC Shift]:
      NOCONCATENATE LOAD *
      RESIDENT [Headcount Category]
      WHERE(MetricIndex = 'Shift');

       

      FOR i = 1 TO 7 STEP 1
      [HC Forecast Table]:
      LET State = IF($(i)=1,'NOCONCATENATE','CONCATENATE');
      $(State) LOAD
        TeamID,
        MetricIndex,
        MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
        LOOKUP('Row $(i)','TeamID',TeamID,'Job Category') AS [Job Category],
        LOOKUP('Row $(i)','TeamID',TeamID,'Billing Code') AS [Billing Code],
        LOOKUP('Row $(i)','TeamID',TeamID,'FTE Percentage') AS [FTE %], 
        LOOKUP('Row $(i)','TeamID',TeamID,'HC Shift') AS [HC Shift],
        'Manila' AS [HC Location],
        '$(i)' as [HC Row],
        [Row $(i)] as Headcount
      RESIDENT [Headcount Table]
      WHERE(NOT IsNull('Row $(i)') AND 'Row $(i)' > 0 AND [HC Location] = 'Manila');

       

      IF '$(Extra)' = '' THEN
      ELSE
        CONCATENATE LOAD
        TeamID,
        MetricIndex,
        MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
        LOOKUP('Row $(i)','TeamID',TeamID,'Job Category') AS [Job Category],
        LOOKUP('Row $(i)','TeamID',TeamID,'Billing Code') AS [Billing Code],
        LOOKUP('Row $(i)','TeamID',TeamID,'FTE Percentage') AS [FTE %], 
        LOOKUP('Row $(i)','TeamID',TeamID,'HC Shift') AS [HC Shift],
        'Chengdu' AS [HC Location],
        '$(i)' as [HC Row],
        [Row $(i)] as Headcount
      RESIDENT [Headcount Table]
      WHERE(NOT IsNull('Row $(i)') AND 'Row $(i)' > 0 AND [HC Location] = 'Chengdu');
       

       

      END IF
      NEXT i  

      FOR i = 9 TO 14 STEP 1
      LET State = IF($(i)=1,'NOCONCATENATE','CONCATENATE');
      $(State) LOAD
        TeamID,
        MetricIndex,
        MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
        LOOKUP('Row $(i)','TeamID',TeamID,'Job Category') AS [Job Category],
        LOOKUP('Row $(i)','TeamID',TeamID,'Billing Code') AS [Billing Code],
        LOOKUP('Row $(i)','TeamID',TeamID,'FTE Percentage') AS [FTE %], 
        LOOKUP('Row $(i)','TeamID',TeamID,'HC Shift') AS [HC Shift],
        'Manila' AS [HC Location],
        '$(i)' as [HC Row],
        [Row $(i)] as Headcount
      RESIDENT [Headcount Table]
      WHERE(NOT IsNull('Row $(i)') AND 'Row $(i)' > 0 AND [HC Location] = 'Manila');

      IF '$(Extra)' = '' THEN
      ELSE
        CONCATENATE LOAD
        TeamID,
        MetricIndex,
        MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
        LOOKUP('Row $(i)','TeamID',TeamID,'Job Category') AS [Job Category],
        LOOKUP('Row $(i)','TeamID',TeamID,'Billing Code') AS [Billing Code],
        LOOKUP('Row $(i)','TeamID',TeamID,'FTE Percentage') AS [FTE %], 
        LOOKUP('Row $(i)','TeamID',TeamID,'HC Shift') AS [HC Shift],
        'Chengdu' AS [HC Location],
        '$(i)' as [HC Row],
        [Row $(i)] as Headcount
      RESIDENT [Headcount Table]
      WHERE(NOT IsNull('Row $(i)') AND 'Row $(i)' > 0 AND [HC Location] = 'Chengdu');
       

       

      END IF


      NEXT i

       

      IF '$(TeamShort)' = 'TQCD SUPP' THEN
      FOR i = 16 TO 17 STEP 1
        LET State = IF($(i)=1,'NOCONCATENATE','CONCATENATE');
        $(State) LOAD
         TeamID,
         MetricIndex,
         MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
         LOOKUP('Row $(i)','TeamID',TeamID,'Job Category') AS [Job Category],
         LOOKUP('Row $(i)','TeamID',TeamID,'Billing Code') AS [Billing Code],
         LOOKUP('Row $(i)','TeamID',TeamID,'FTE Percentage') AS [FTE %], 
         LOOKUP('Row $(i)','TeamID',TeamID,'HC Shift') AS [HC Shift],
         'Manila' AS [HC Location],
         '$(i)' as [HC Row],
         [Row $(i)] as Headcount
        RESIDENT [Headcount Table]
        WHERE(NOT IsNull('Row $(i)') AND 'Row $(i)' > 0 AND [HC Location] = 'Manila');
      NEXT i
      END IF

      [Total HC]:
      NOCONCATENATE LOAD
      TeamID,
      MetricIndex,
      MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
      [Row 8] AS [Total Billable HC],
      [Row 15] AS [Total Team Overhead],
      [Total Headcount],
      [Total Billable FTE],
      [Total Non-Billable FTE],
      [Total FTE],
      [HC Location]
      RESIDENT [Headcount Table];

      [HC Change Rows]:
      NOCONCATENATE LOAD
      TeamID,
      MetricIndex,
      MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
      [New Entries],
      [Tranfers to Other Teams / LOA],
      [Attrition],
      [Net Change in Headcount],
      [Maternity Leave / LOA],
      [HC Location]
      RESIDENT [Headcount Table];

      [HC Seats]:
      NOCONCATENATE LOAD
      TeamID,
      MetricIndex,
      MONTH(MetricIndex)&'-'&RIGHT(YEAR(MetricIndex),2) as [HC MMM-YY],
      [Daytime Production Seats],
      [Nighttime Production Seats],
      [Rotating Shift Seats],
      [HC Location]
      RESIDENT [Headcount Table];


      DROP TABLE [Headcount Table];
      DROP TABLE [Headcount Category];
      DROP TABLE [Job Category];
      DROP TABLE [Billing Code];
      DROP TABLE [FTE Percentage];
      DROP TABLE [HC Shift];

       

      I only want to replace the Headcount and all other data loaded by the scripts below Headcount should be retained.

       

      Thanks in advance.

       

      Salie