Want to get columns to Rows

[ferha_jafri]

Hello Experts,

I am looking for a solution in which I have data in different columns and I want to get the data in different columns under one column.

PFA with the sample data on which I am currently working on.Name of the qvw is CommentryTest.

The output I am looking for is attached in excel. Name of the excel CommentryOutput.

[sunny_talwar · Accepted solution]

Try this

Table:
CrossTable (Field, Value, 4)
LOAD RowNo() as RowNum, * INLINE [
    Business, Place, Metrics, Theme1, Volume1, Theme2, Volume2, Theme3, Volume3, Rational1, Rational2, Rational3
    Business1, Place1, Metric1, BA, 10, Clean, 20, Receipt, 30, Test1, Test2, Test3
    Business1, Place1, Metric1, Clear, 5, BA, 10, Non Receipt, 20, Test4, Test5, Test6
    Business2, Place2, Metric2, BA, 50, Clean, 10, Receipt, 60, Test7, Test8, Test9
    Business2, Place2, Metric2, Clear, 20, BA, 30, Non Receipt, 30, Test10, Test11, Test12
];

tmpTable:
LOAD *,
	 PurgeChar(Field, '0123456789') as TruncField,
	 KeepChar(Field, '0123456789') as FieldNum
Resident Table;

DROP Table Table;

FinalTable:
LOAD Distinct
	 RowNum,
	 Business,
	 Place,
	 Metrics,
	 FieldNum
Resident tmpTable;

FOR i = 1 to FieldValueCount('TruncField')

	LET vField = FieldValue('TruncField', $(i));
	TRACE $(vField);
	
	Left Join (FinalTable)
	LOAD Distinct
		 RowNum,
		 Business,
		 Place,
		 Metrics,
		 FieldNum,
		 Value as [$(vField)1]
	Resident tmpTable
	Where TruncField = '$(vField)';
	
NEXT

DROP Table tmpTable;

[techvarun]

Try the below script

Test:
LOAD *,RowNo() INLINE [
Business, Place, Metrics, Theme1, Volume1, Theme2, Volume2, Theme3, Volume3, Rational1, Rational2, Rational3
Business1, Place1, Metric1, BA, 10, Clean, 20, Receipt, 30, Test1, Test2, Test3
Business1, Place1, Metric1, Clear, 5, BA, 10, Non Receipt, 20, Test4, Test5, Test6
Business2, Place2, Metric2, BA, 50, Clean, 10, Receipt, 60, Test7, Test8, Test9
Business2, Place2, Metric2, Clear, 20, BA, 30, Non Receipt, 30, Test10, Test11, Test12
];

Temp:
Load
Business,
Place,
Metrics,
//Volume,
Theme1 as Theme,
Volume1 as Volume,
Rational1 as Rational
Resident Test;

concatenate (Temp)
Load
Business,
Place,
Metrics,
//Volume,
Theme2 as Theme,
Volume2 as Volume,
Rational2 as Rational Resident Test;

concatenate (Temp)
Load
Business,
Place,
Metrics,
//Volume,
Theme3 as Theme,
Volume3 as Volume,
Rational3 as Rational
Resident Test;

Drop Table Test;

Exit SCRIPT;