How to combine different statuses into one field

[HegeS]

I work with contracts that are sent to external users for digital signing. One document must be signed by 2 people, one "leader", one "member"

I want a table that shows all documents that are being processed  and detect the person that has not signed.

The table is similar to:

Left join (DOCUMENT)

LOAD

    MEMBER,

    LEADER,

    ID

    STATUS

    IF(WILDMATCH(STATUS,'*SIGNED*')=1,1,0) AS SIGNED.1.0,

FROM

BASE

(qvd);

I need one field that tells me if the member has signed, or the leader has signed or if none has signed. 

Ive tried multiple things, but they end up giving me duplicates of the document, with one row for the member and another for the leader

Example:

Left join (Document)

LOAD ID,

if(len(trim([LEADER]))>0 and SIGNED.1.0=1 ,'LEADER',
if(len(trim(MEMBER))>0 and SIGNED.1.0=1 ,'MEMBER',
if((len(trim(LEADER))>0 and SIGNED.1.0=0) AND (len(trim(MEMBER))>0 and SIGNED.1.0=0),'NONE'))) as WHO.SIGNED

Resident Document;

Or master item:

=if(len(trim(LEADER))>0 and [SIGNED.1.0]=1 ,'YES')

This gives duplicates and if I try to use the "Include null values" it removes all the documents where the MEMBER has signed. I need both...

Anyone?

[rubenmarin]

Hi, you need to detect where the duplicates comes, maybe  it's in the 'BASE' table where there are more than one record for each ID, in that case you can do a previous load to keep only one row by ID, somethinglike:

BASE_JOINED:
LOAD
    ID,
    MEMBER,
    MEMBER_STATUS,
    IF(WILDMATCH(STATUS,'*SIGNED*')=1,1,0) AS MEMBER_SIGNED.1.0,
FROM
BASE(qvd)
WHERE not IsNull(MEMBER);

Left Join(BASE_JOINED)
LOAD
    ID,
    LEADER,
    LEADER_STATUS,
    IF(WILDMATCH(STATUS,'*SIGNED*')=1,1,0) AS LEADER_SIGNED.1.0,
FROM
BASE(qvd)
WHERE not IsNull(LEADER);

If each ID has more than one MEMBER or LEADER you will need some extra logic to avoid duplicates, maybe just addind a composite key by ID and MEMBER or LEADER and use Exists() to avoid loading duplicates.