Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Maybe have a look at
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
could you share a sample qdf with mock data explaining better what's your expected result?
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
Hi Erica,
Thanks for the help but I am looking on the data model side
Best Regards
Serena
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 ?
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
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;
you can create a calculated dimension for SIG as below:
Aggr( if( count(distinct SIG) = 1, SIG, 'HIPS and NIPS' ), CVE )