Adding new column with value Y and N based on value exist in another column

[pgloc2020]

Hello,

I have a "Deposit Id" column with either deposit ID exist or not. Now I want to add an additional column which checks if deposit ID exist then placeY and if not then place N.

I tried many expressions in the Load statement but either I am getting Y or N but not both

[DEPOSIT]:

LOAD
[DEPOSIT_ID],

If([DEPOSIT_ID]>=1,'Yes','No') AS [DepositReceived]
// if(([DEPOSIT_ID])= '-','N','Y')AS [DepositReceived]
// If(Exists([DEPOSIT_ID]), 'Y', 'N') AS [DepositReceived]

// IF(Not IsNull([DEPOSIT_ID]), 'Yes', 'No') AS [DepositReceived]

FROM [/DEPOSIT.qvd]
(qvd);

I also tried the resident load statement but that also does not give me both Y and N values. 

[temp]:

Load
*,
If(Exists([DEPOSIT_ID]>=1), 'Y', 'N') AS [DepositReceived]

Resident [DEPOSIT];
DROP TABLE [DEPOSIT];

Please let me know how  I can achieve both Yes and No values based on if Deposit ID exists or not. 

Deposit ID is an alphanumeric characters something like 1-2AB6C45

Many thanks!

[brunobertels]

try 

if(len(DEPOSIT_ID])>0,'Y','N') as [DepositReceived]

[Clement15]

Hello, could this work for you?

TEST2:
Load
*,
if(len(IDD)>=1,'Y','N')
Inline [
'IDD' , 'Name'
'ID 1','James;Nick'
'ID 2','Bob;Tom'
,'JHON'
];

Adapting it to your data shouldn't be complicated.

[pgloc2020]

Hi, 

Thank you for the expression, I tried it in the load statement but all I am getting is Y not N.

[pgloc2020]

if(len([DEPOSIT_ID])>1,'Y','N') as [DepositReceived], is also giving me just Y not N.

[stevedark]

Hi @pgloc2020 

I think the problem you are hitting here is that you are doing the Deposit Received field on the Deposits QVD, so by definition every row in there is a deposit.

How does the Deposit table link to the rest of the data model? This is important for making this work.

Perhaps the simplest way to deal with this will be to do an ApplyMap statement. Assuming you have an Account table with an AccountID, and AccountID is a property it would look something like this:

// Create a mapping table to check for a deposit by account
Map_DepositExists:
MAPPING LOAD
   AccountID,
   'Yes' as exists
FROM [/DEPOSIT.qvd] (qvd);

// Load the accounts table and add the exists flag
Account:
LOAD
   AccountID,
   ApplyMap('Map_DepositExists', AccountID, 'No') as [Deposit Exists],
   ... load rest of account table ...
FROM [/ACCOUNT.qvd] (qvd);

// Now load the rest of the deposit QVD
Deposit:
LOAD
   ... etc. ...

Obviously your table and field names are likely to be different, but hopefully this points you in the right direction.

If it doesn't, perhaps you could share more of the load script, so we can see how the tables associate with each other.

Cheers,
Steve

[pgloc2020 · Accepted solution]

Okay, so here is what I did and it works-

First load the [SERV_REQUEST] table and then load the [Deposit] table joining with [SERV_REQUEST] table and use the Resident load like below-

[FinalTable]:

LOAD
*,
If([DEPOSIT_ID]>=1,'Yes','No') AS [DepositReceived]

Resident [SERV_REQUEST];

DROP TABLE [SERV_REQUEST];

Thank you all for the input.