LookUp Function

[MP1]

Hi, 

I have dataset that is in below format. There are around 100 columns in the original dataset.

Name	A1	A2	A3	A4
Sam	1
Linda			1
Richard	1
Sandy		1
Ben				1
Tom				1

I want to have a extra column which would return the name of column if the value is '1'.

Like Below table. What functions/logic can be used to achieve this. 

Name	A1	A2	A3	A4	Sample
Sam	1				A1
Richard	1				A1
Sandy		1			A2
Ben				1	A4
Tom			1		A3

Thanks In Advance for your help.

[marcus_sommer]

I would probably use a crosstable like:

t1: crosstable(Type, Value, 1) load * from Source;

t2: load * resident t1 where Value = 1;

- Marcus

[MP1]

Hi Marcus,

Thanks for your reply.

I'm trying to test your query but I'm getting error for invalid path. For source I copied and used the same path that I used for load and select statement for the main table. I even tried using the table name as source but still getting error.

Would you have any idea what could be causing the error?

-MP

[marcus_sommer]

If you want to load data from a database you need to create a connection to the database and to store these information within a lib. Usually it's helpful to create the (first shot of) the load-statement with the wizard.

- Marcus