Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

7 Replies
its_anandrjs
Not applicable

Re: partial reload

Hi,

You have to add a syntax before the load script of the table like

Add Drop Tablename;

Tablename:

Add Load script

From Your Path

Rgds

Anand

Not applicable

partial reload

Hi Anand,

thanks for that, but where in the script should I add it? Also, I tried the ADD/Replace syntax, but it it still reloads the scripts after Headcount..

Thanks again

Not applicable

Re: partial reload

Hi Anand,

thanks for that, but where in the script should I add it? Also, I tried the ADD/Replace syntax, but it it still reloads the scripts after Headcount..

Thanks again

its_anandrjs
Not applicable

Re: partial reload

Hi,

Thanks before the load statement like which table you want to reload put

Add Load

...

...

from table

Ex:-

[Headcount Table]:

Add

NOCONCATENATE  LOAD *

FROM

[$(QVDNum)]

(qvd);

If any table has concatenate put like

Add Concatenate Load

Rgds

Anand

Not applicable

Re: partial reload

Hi,

Should I delete the scripts below it? Or the 'From Table' will stop the reloading of the next script below?

thank you again! =)

its_anandrjs
Not applicable

Re: partial reload

Hi,

No after putting the code on the table do partial reload from File -> Partial Reload after running this code it will only reload that table before you put Add.

Also keep take backup of your qvw before partial reload.

Thanks

Rgds

Anand

its_anandrjs
Not applicable

Re: partial reload

Hi,

Hope you got correct answer if so mark the post as correct answer on my post.

Let me know

Rgds

Anand