Incremental Load using a unique key

bobbydave — Sat, 16 Nov 2024 03:57:54 GMT

I have a table of Vulnerabilities (lets say date is 01/04/2019

I load this table and then this creates an nPrinting report the same week..

It comprises of %Vulnerability and [Vulnerability Date] and a Comment

I then reload this nPrinting report 01/04/2019into QlikView and load this weeks file 07/04/2019 and concatenate and save this into a QVD.

What I am trying to achieve is

Whenever a comment is inputted in the nprinting report and that is reloaded, then to take that comment and put it in the newest weekly file Comment row.
If %vulnerability from the two different dates match, I want to delete the oldest %vulnerability and only use the newest %Vulnerability.

I am trying to use Incremental load and it keeps doubling my numbers which means its not deleting the

Report:
LOAD 
 IF(
      @7 & '-' & 
      ApplyMap('Qualys_Hostname', @1, 'OTHER') & '-' & 
      ApplyMap('Qualys_Description', @1, 'OTHER') & '-' & 
      IF(TextBetween(MapSubString( 'Keyword_Mapping' , @8), '/', '\') = '/SSL', 'SSL', TextBetween(MapSubString( 'Keyword_Mapping' , @8), '/', '\')) = '-OTHER-OTHER-', NULL(),
      IF(
      	@7 & '-' & 
      	ApplyMap('Qualys_Hostname', @1, 'OTHER') & '-' & 
     	ApplyMap('Qualys_Description', @1, 'OTHER') & '-' & 
      	IF(TextBetween(MapSubString( 'Keyword_Mapping' , @8), '/', '\') = '/SSL', 'SSL', TextBetween(MapSubString( 'Keyword_Mapping' , @8), '/', '\')) = '-', NULL(),
      @7 & '-' & 
      ApplyMap('Qualys_Hostname', @1, 'OTHER') & '-' & 
      ApplyMap('Qualys_Description', @1, 'OTHER') & '-' & 
      IF(TextBetween(MapSubString( 'Keyword_Mapping' , @8), '/', '\') = '/SSL', 'SSL', TextBetween(MapSubString( 'Keyword_Mapping' , @8), '/', '\'))))
																					as %Vulnerability,
'' 																					as Comment,
	 
     Date(Date#(left(SubField(FileName(), '_', 3),8), 'DDMMYYYY'),'DD-MM-YYYY')				as [Vulnerability Date]
FROM
[$(vSource)\CSVs\Scan_Report_*$(vcurrentyear).csv]
(txt, codepage is 1252, explicit labels, delimiter is ',', msq, header is 4 lines)
//where Date(Date#(left(SubField(FileName(), '_', 3),8), 'DDMMYYYY'),'DD/MM/YYYY') > $(Last_Updated_Date)
;
Last_Updated_Date:
LOAD Date(max([Vulnerability Date])) as Max_Date
Resident Report;

Let Last_Updated_Date = peek('Max_Date', -1, 'Last_Updated_Date');
TRACE >>> $(Last_Updated_Date);

Concatenate
LOAD 
	 %Vulnerability, 
          [Vulnerability Date],
          Comment
From
Report_Comment.qvd where not exists %Vulnerability;

topic Incremental Load using a unique key in QlikView

Incremental Load using a unique key