Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

meizheng
New 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

Tags (1)
1 Solution

Accepted Solutions
ericasense
New Contributor III

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

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;

10 Replies
MVP
MVP

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

ericasense
New Contributor III

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

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
Honored Contributor II

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

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

meizheng
New Contributor III

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

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
New Contributor III

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

Hi Erica,

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

Best Regards

Serena

agigliotti
Honored Contributor II

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

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
New Contributor III

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

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
New Contributor III

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

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
Honored Contributor II

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

you can create a calculated dimension for SIG as below:

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