topic partial reload in QlikView

partial reload

Mon, 26 Sep 2011 13:12:04 GMT

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');

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

Re: partial reload

its_anandrjs — Mon, 26 Sep 2011 13:14:41 GMT

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

partial reload

Mon, 26 Sep 2011 13:17:35 GMT

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

Re: partial reload

Mon, 26 Sep 2011 13:18:10 GMT

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

Re: partial reload

its_anandrjs — Mon, 26 Sep 2011 13:21:22 GMT

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

Re: partial reload

Mon, 26 Sep 2011 13:22:59 GMT

Hi,

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

thank you again! 😃

Re: partial reload

its_anandrjs — Mon, 26 Sep 2011 13:29:10 GMT

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

Re: partial reload

its_anandrjs — Mon, 26 Sep 2011 13:57:42 GMT

Hi,

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

Let me know

Rgds

Anand