topic Re: Want to get columns to Rows in QlikView

Want to get columns to Rows

ferha_jafri — Sat, 16 Nov 2024 02:45:59 GMT

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.

Re: Want to get columns to Rows

sunny_talwar — Mon, 12 Aug 2019 12:16:42 GMT

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;

Re: Want to get columns to Rows

techvarun — Mon, 12 Aug 2019 12:21:59 GMT

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;