topic Concatenate and exist in App Development

Concatenate and exist

Jonathan_Alm — Thu, 02 Dec 2021 14:01:25 GMT

Hi,

I have a section access issue where we have a central file for general section access, and a custom file for users who need a different access in some applications.

The custom file consist of the following rows:

NTNAME	ACCESS	PERMISSIONS
ZZZ/LISA	ADMIN	*

The general section access file consist of the following rows:

NTNAME	ACCESS	PERMISSIONS
ZZZ/LISA	USER	SALES
ZZZ/LISA	USER	ORDERS
ZZZ/MARK	USER	SALES
ZZZ/ADAM	ADMIN	*
ZZZ/CLAIRE	USER	SALES
ZZZ/CLAIRE	USER	ORDERS

I want to remove LISAS rows in the general table and replace them with new access, but at the same time keep MARK, ADAM and CLAIRES rows.
I have tried the following syntax, but that removes CLAIRES second row:

LOAD * INLINE
[
NTNAME, ACCESS, PERMISSIONS
ZZZ/LISA, ADMIN, *
];

Concatenate
LOAD distinct
NTNAME,
"ACCESS",
PERMISSIONS
FROM
$(vG.ExtractPath)/SectionAccess.qvd (qvd) where not exists(NTNAME);

Result = Correct for LISA but CLAIRE's second row is missing.

NTNAME	ACCESS	PERMISSIONS
ZZZ/LISA	ADMIN	*
ZZZ/MARK	USER	SALES
ZZZ/ADAM	ADMIN	*
ZZZ/CLAIRE	USER	SALES

I want the following result.

NTNAME	ACCESS	PERMISSIONS
ZZZ/LISA	ADMIN	*
ZZZ/MARK	USER	SALES
ZZZ/ADAM	ADMIN	*
ZZZ/CLAIRE	USER	SALES
ZZZ/CLAIRE	USER	ORDERS

Any suggestions?

Kind regards, Jonathan

Re: Concatenate and exist

stevejoyce — Thu, 02 Dec 2021 14:21:31 GMT

@Jonathan_Alm This is explained in this article:

https://community.qlik.com/t5/Knowledge/WHERE-NOT-EXISTS-clause-returns-only-one-row-for-each-non/ta-p/1716931

something like this...

data:
load *
,NTNAME as tempNTNAME
;
load
*
from https://community.qlik.com/t5/App-Development/Concatenate-and-exist/td-p/1866050
(html, utf8, embedded labels, table is @1)
;

concatenate(data)
load *
from https://community.qlik.com/t5/App-Development/Concatenate-and-exist/td-p/1866050
(html, utf8, embedded labels, table is @2)
where not exists(tempNTNAME,NTNAME)
;

drop field tempNTNAME;
exit script;

Re: Concatenate and exist

Jonathan_Alm — Thu, 02 Dec 2021 14:34:45 GMT

Hi,

works like a charm! Thanks

Kind regards, Jonathan