I have a simple if statement which seem to be not working.
Im trying to create a new variable, which based on the results of 'Count' will assign a value to another one. If the count is 0, the value is D, otherwise it is A
I tried the following:
IF(COUNT([X_1])=' ','D','A') AS Var1
but it is not working. What is the correct way of doing this?
EDIT
to elaborate more on my question. I have 2 tables, which looks like this:
Table TD
CID
OID
1
x12
1
x23
1
x32
2
x41
2
x53
3
x62
The other table, looks like this:
Table CD
CID
CNAME
1
John Smith
2
Max Mustermann
3
Edith Bingo
4
Albert Reckel
5
Sam Retro
6
Teno
What i want to do, is to generate a new column for the 2nd table, which is based on the count of OID in the 1st table, i.e., this column should calculate, if count(OID) is NULL, the value should be D, else it should be A.
This is how my I try to do the left join at the moment:
left join (CD)
load
IF(COUNT([OID])=0,'Dormant','Active') AS Var1
Resident TD
And this new Var1 variable, will be used in a plot afterwards.
Do not mix this in your JOIN. Because you want to load a variable (can contain one value in two formats, nothing more) you have to calculate the count outside of everything else.
Please do not change your requirement halfway in the discussion, as you will make an entire thread useless. Many people spend their time trying to help you, and time is never a free resource for them.
Your changed requirement has to be implemented in a different way. You are now asking for a Count() calculated per CID value. You can't use a variable for that, as a variable can only contain a single value (in two formats).
A JOIN like you propose brings an additional problem. CIDs that are not present in table TD will create a NULL value instead of value 'A'. So we have to resort to a trick that uses a mapping table and a call to ApplyMap. Something like this:
