Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Incremental Load using a unique key

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;

 

Labels (3)
0 Replies