Concat 2 tables and check if fieldX=A and fieldX=B for each item

[Anonymous]

Hi,

I have a table as shown below. XXX is not unique.

Right now it is displayed as below. I would like to show either 'ABC', 'DEF' (if it exists in only one of the respective table) or 'ABC and DEF' (if it exists in both tables) for "type" in the final table. Meaning I would like to show 'HIPS and NIPS' instead of showing the cartesian product(?).

Thought of doing an ApplyMap to the temporary ABC or DEF tables to determine the "type"

something like this logic:

     if ApplyMap to 'ABC' not null => 'ABC',

     else if ApplyMap to 'DEF' not null => 'DEF',

     else if ApplyMap to 'ABC' and to 'DEF' not null => 'ABC & DEF'

     else 'N/A'

Is there a better way of achieving my desired results?

Update:

This is my codes for the final table

CVE:

Load

nipsName as CVE.Name,

    nipsCVE as CVE,

    sigFrom

Resident nipsCVE;

Concatenate

Load

hipsName as CVE.Name,

    hipsCVE as CVE,

    sigFrom

Resident hipsCVE;

Thanks and regards,

Serena

[swuehl]

Maybe have a look at

How to compare 2 tables and put non-matching results?

[ericasense]

Hi Serena

You could use the "concat" text function, either for a dynamic result in the chart or as a calculated field in the script.

Concat basically provides a list of striing values, which can be delimited however you like. We could delimit using the string " and " to replicate this.

=concat(distinct sigFrom, ' and ')

Erica

[agigliotti]

could you share a sample qdf with mock data explaining better what's your expected result?

[Anonymous]

Hi

This is the mock load script. I would like to display 'HIPS and NIPS' for the sigFrom column if it exists in both nips and hips tables.

//Load NIPS list

testnipsCVE:

//to remove fields with empty CVE cell

Load

    "testnipsName",

    CVE as testnipsCVE,

    'NIPS' as testsigFrom

    Where not "CVE" like '' and not CVE like 'C';

LOAD

    Name as "testnipsName",

    if(WildMatch(CVE, '*'&chr(10)&chr(10)&'*'),

    Subfield(replace(CVE,'...',''),chr(10)&chr(10)), //for CVE fields split by br br

        if(WildMatch(CVE, '*C*C*'),

            'C'&replace(Subfield(CVE, 'C'), '...', ''), //for CVE fields concat tgthr; remove trailing '...'

                        CVE)) as CVE

    Inline [

Name, CVE

    'Lorem ipsum dolor sit amet', 'CVE-2014-0873CVE-2014-0874'

    'Lorem ipsum', 'CVE-2011-1440

CVE-2011-2338

CVE-2011-2339'

]

    Where not CVE like '...';

//Load HIPS list  

testhipsCVE:

Load

Name as testhipsName,

'CVE'&replace(TextBetween(Name, 'CVE', ' '), ')', '') as testhipsCVE,

    'HIPS' as testsigFrom

Inline [

Name

'Adobe Flash Player CVE-2017-11292 Type Confusion Remote Code Execution Vulnerability'

'Microsoft Internet Explorer CVE-2014-0312 Memory Corruption Vulnerability'

]

Where not IsNull(TextBetween(Name, 'CVE', ' '));

testCVE:

Load

testnipsName as testCVE.Name,

    testnipsCVE as testCVE,

    testsigFrom

Resident testnipsCVE;

Concatenate

Load

testhipsName as testCVE.Name,

    testhipsCVE as testCVE,

    testsigFrom

Resident testhipsCVE;

drop table testnipsCVE, testhipsCVE;

Thanks and regards

Serena

[Anonymous]

Hi Erica,

Thanks for the help but I am looking on the data model side

Best Regards

Serena

[agigliotti]

what's the expected result do you have in final table ?

if a CVE like below is in both tables you'd have only one row in final table ?

CVE   TYPE   SIG

200     ABC   HIPS

200     DEF   NIPS

how final table should look like in your data model ?

[Anonymous]

Hi Andrea,

The desired result would be:

CVE          SIG

200     HIPS and NIPS

I'd like to show only first three rows of below table

[ericasense · Accepted solution]

Hi Serena,

The concat() function will also work in the script.

I would aggregate by name with this function then join to the main table. For example:

left join(CVE)

LOAD     CVE,

               concat(distinct sigFrom, ' and ') as sigFromConcat

resident CVE

Group by CVE;

[agigliotti]

you can create a calculated dimension for SIG as below:

Aggr( if( count(distinct SIG) = 1, SIG, 'HIPS and NIPS' ), CVE )