Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
There is lots of question about creating new column in the script, but unfortunately I couldn't find answer for my question.
I need to create 1/0 (True/False) column based on the other 3 columns value. So if I have in my existing columns at least one 0 it should have 0, if there is no 0 then it should be 1.
Any help?
Thank you,
Andrzej
t:
load * inline [
f1,f2
a,5
a,0
a,12
b,12
b,9,
];
[0 or 1]:
load
f1,
min(if(f2>0,1,0)) as f3
Resident t
group by f1;
drop table t;
load
existingcolumn,
if(existingcolumn=0, 0, 1) as newcolumn
,,,,
from
....
// true false
load
existingcolumn,
if(existingcolumn=0, false(), true()) as newcolumn
I am sorry, I didn't explain well what I need. Here is an example:
a | 5 | a | 0 |
a | 0 | b | 1 |
a | 12 | ||
b | 12 | ||
b | 9 | ||
b | 10 |
so first 2 columns is what I have and 3-4 what I need
"a" and "b" are names of the partner so if this partner has atleast one 0 then in the new column it should be 0 othervise 1.
I don't mind if result will look like:
a | 0 |
a | 0 |
a | 0 |
b | 1 |
b | 1 |
b | 1 |
Hello
if((column1=0 or Column2=0 or column3=0), 0, 1)
Regards
First find the minimum value of your second column with a group by and then use that value to determine the 0 or 1 for the new column
LOAD *,
If(MinValue = 0, 0, 1) As NewColumn
LOAD partner, Min(Column2) As MinValue
GROUP BY partner
Resident MyTable
t:
load * inline [
f1,f2
a,5
a,0
a,12
b,12
b,9,
];
[0 or 1]:
load
f1,
min(if(f2>0,1,0)) as f3
Resident t
group by f1;
drop table t;
Hi Dick,
I can't make this code working can I sent you PM with script I have?
Adjusted script done what I needed:) thank you