if statement based on calculation

[#1]

Hi,

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.

Does this make things more clear now?

[Anil_Babu_Samineni]

Try this?

If(COUNT(X_1)=0,'D','A') AS Var1

[matteo_mi]

Hi,

go to settings, variable overview

and create the new variable with your needs.

hope this  help

[madhumitha]

Hello Abdullah,

You cannot assign values to variables with 'AS' key word. It should be either 'LET' or 'SET'. You can also use variable overview tab to create new variables.

Hope this helps

[#5]

does not work.

[#6]

Im not assigning a value to a variable, I want the value to be calculated in the script based on this condition. Then I want to use this value for plotting.

[#7]

How can I then use this variable in a plot? will it adjust automatically to the dimension I use for the plot (e.g., the count for each ID should not be 0)?

[matteo_mi]

Hi Abdullah,

to use variable in this he script you should use  SET or LET as explained above by Madhu Mitha.

Maybe what you are trying to accomplish have to be done in other way, so if you want you can

explain more in detail what is your goal posting an example.

best regards

[marcus_sommer]

It's not quite clear what you are trying to do. Do you want check if this field-value is empty, NULL or contained a white-space then you need a check on the lenght, for example len(X_1) = 1 or len(trim(X_1)) = 0 or do you want to count the fieldvalues and check these amounts with something like >= AnyNumber. To count fieldvalues within a load meant you need to use an aggregation load with group by.

- Marcus

[Peter_Cammaert]

If you want to do this in your load script, then follow these steps. We are assuming that X_1 is a column in a resident table called (for example) Table_1. If the table has a different name, then change the code accordingly.

Table_Count_X_1:

LOAD IF (Count(X_1)=0, 'D', 'A') AS Flag_X_1

RESIDENT Table_1; // Modify if resident table has a different name

LET Var1 = Peek('Flag_X_1'); // Var1 now contains either D or A

DROP Table Table_Count_X_1;

If you want to count the number of unique values in X_1, then add a DISTINCT keyword before X_1.

Best,

Peter