Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
markgraham123
Valued Contributor

get field names as dimension by it's values

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.

Tags (1)
7 Replies
Partner
Partner

Re: get field names as dimension by it's values

you can use in script

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

markgraham123
Valued Contributor

Re: get field names as dimension by it's values

Jonathan,

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

Thanks

Re: get field names as dimension by it's values

is This?

I've upload the new one

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
markgraham123
Valued Contributor

Re: get field names as dimension by it's values

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

Partner
Partner

Re: get field names as dimension by it's values

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;

Partner
Partner

Re: get field names as dimension by it's values

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;

Re: get field names as dimension by it's values

Hi,

maybe another solution could be:

QlikCommunity_Thread_231861_Pic2.JPG

QlikCommunity_Thread_231861_Pic4.JPG

by selecting either

QlikCommunity_Thread_231861_Pic1.JPG

or

QlikCommunity_Thread_231861_Pic3.JPG

or

QlikCommunity_Thread_231861_Pic9.JPG

or comparing in a pivot table:

QlikCommunity_Thread_231861_Pic5.JPG QlikCommunity_Thread_231861_Pic6.JPG

QlikCommunity_Thread_231861_Pic7.JPG  QlikCommunity_Thread_231861_Pic8.JPG

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