Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try this?
If(COUNT(X_1)=0,'D','A') AS Var1
Hi,
go to settings, variable overview
and create the new variable with your needs.
hope this help
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
does not work.
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.
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)?
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
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
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