get field names as dimension by it's values

[markgraham123]

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.

[jpenuliar]

you can use in script

If(Field>10,'FieldName') as testField,

[markgraham123]

Jonathan,

This is now where related to my requirement. but appreciate your help.

Thanks

[Anil_Babu_Samineni]

is This?

I've upload the new one

[markgraham123]

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

[jpenuliar]

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;

[Jason_Michaelides]

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;

[MarcoWedel]

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