Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to create a new field which will have field names with respect to the values in it.
Here is my data set:
ID, Value1, Value2, Value3, Value4, Value5
A, 10, 20, 30, 40, 50
B, 1, 2, 33, 44, 55
C, 0, 1, 2, 300, 400
I want to create a new field - 'Test'
if any field has its value greater than 10, then i want to display the filed names.
o/p field:
ID, Test
A, Value2, Value3, Value4, Value5
B Value3, Value4, Value5
C Value4, Value5
Any help is highly appreciated.
Thanks.
you can use in script
If(Field>10,'FieldName') as testField,
Jonathan,
This is now where related to my requirement. but appreciate your help.
Thanks
is This?
I've upload the new one
Anil,
I'm trying to get only the field names with respect to ID which has greater than 10 value.
o/p field:
ID, Test
A, Value2, Value3, Value4, Value5
B Value3, Value4, Value5
C Value4, Value5
try loading Table1 as CrossTable
Table1:
LOAD * Inline
[
ID,Value1, Value2, Value3, Value4, Value5
A,10,20,30,40,50
B,1,22,33,44,55
C,0,100,200,300,400
];
Table2:
CrossTable
Load *
Resident
Table1;
NoConcatenate
Table3:
LOAD ID,
Type
RESIDENT Table2
Where Value > 10;
Drop Table Table2;
Here you go - hope it helps.
Jason
Table1:
LOAD * Inline
[
ID,Value1, Value2, Value3, Value4, Value5
A,10,20,30,40,50
B,1,2,33,44,55
C,0,1,2,300,400
];
CrossTable:
CrossTable(ValueField,Value)
LOAD * RESIDENT Table1;
JOIN (Table1)
LOAD
ID,
Concat(If(Value>10,ValueField),',') AS Test
RESIDENT CrossTable
GROUP BY ID;
DROP TABLE CrossTable;
Hi,
maybe another solution could be:
by selecting either
or
or
or comparing in a pivot table:
table1:
LOAD * INLINE [
ID, Value1, Value2, Value3, Value4, Value5
A, 10, 20, 30, 40, 50
B, 1, 2, 33, 44, 55
C, 0, 1, 2, 300, 400
];
tabValues:
CrossTable (ValueName, Value)
LOAD * Resident table1;
tabValAttr:
LOAD Distinct
Value,
Value>10 as [greater than 10],
Value>20 as [greater than 20],
Value>50 as [greater than 50],
Value<=10 as [less equal 10],
Value<=20 as [less equal 20],
Value<=50 as [less equal 50]
Resident tabValues;
tabValAttr2:
CrossTable (ValueAttribute, ValAttrVal)
LOAD * Resident tabValAttr;
Right Join (tabValAttr2)
LOAD Distinct ValAttrVal
Resident tabValAttr2
Where ValAttrVal;
DROP Field ValAttrVal;
hope this helps
regards
Marco