Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
See script below. I first load a table from xlsx where I defined special users (USERID) that can see multiple USERPROFILERS, then I pull data from a qvd with all users and only want to add those users to the above table that have not been already mentioned there. I tried to do this with where not exists, but is does not seem to work and I get only those users that actually do exist in the upper table (as if I used where exists() function)
Please help!
section access;
Users:
LOAD ACCESS,
upper(USERID) as USERID,
upper(USERPROFILER) as USERPROFILER
FROM
$(vPathINPUT)Section Access.xlsx
(ooxml, embedded labels, table is [SECTION ACCESS])
where 1 = 1;
Concatenate (Users)
Load *,
USERID2 as USERID
where not Exists(USERID, USERID2)
;
LOAD 'USER' as ACCESS,
'TEVANL\'&upper(USERPROFILER) as USERID2,
upper(USERPROFILER) as USERPROFILER,
upper(USERBUSINESSUNIT) as USERBUSINESSUNIT
FROM
$(vPathQVD_DM_SFE)DM_SFE_DataProfiler.qvd
(qvd)
where USERBUSINESSUNIT <> 'Team A';
Load *,
USERID2 as USERID
where not Exists(USERID, USERID2)
When a USERID2 value is found that doesn't exist in the excel file then you add it as USERID. Then it exists as USERID. So any other records with the same USERID2 value are skipped.
That doesn't explain why no records are loaded from the qvd file though. Perhaps there are no records that don't have A as userbusinessunit.
"Use only
where not Exists(USERID)
I have found the ssue, was a misspelling somewhere.. dohh