Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
meizheng
Contributor III
Contributor III

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

Hi,

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

Untitled.png

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(?).

screenshot.PNG

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

1 Solution

Accepted Solutions
ericasense
Contributor III
Contributor III

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;

View solution in original post

10 Replies
swuehl
MVP
MVP

ericasense
Contributor III
Contributor III

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
Partner - Master III
Partner - Master III

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

meizheng
Contributor III
Contributor III
Author

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

meizheng
Contributor III
Contributor III
Author

Hi Erica,

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

Best Regards

Serena

agigliotti
Partner - Master III
Partner - Master III

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 ?

meizheng
Contributor III
Contributor III
Author

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

testCVEtabl.PNG

ericasense
Contributor III
Contributor III

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
Partner - Master III
Partner - Master III

you can create a calculated dimension for SIG as below:

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