Removing duplicate/update

bobbydave — Sat, 16 Nov 2024 04:10:33 GMT

Qualys:
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 [Action Status],     
      ''                      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);

left Join
LOAD %Vulnerability,
[Action Status],
[Vulnerability Date],
Comment
FROM
[$(vQVDs\Test_Qualys.qvd]
(qvd) where not exists (%Vulnerability);

I am joining the above qvd to the weekly excel files that are injested.

What I want is to compare %Vulnerability and use Comment coming from the QVD file.

Am I using the correct join?

topic Removing duplicate/update in QlikView

Removing duplicate/update