How to convert column into row in qlikview sql ?

[#1]

HI,

I am new to qlikview.

i would like to convert column  data into row.

example :

Field Key

100    1

200     2

300     3

400     4

500   5

output :

1        2          3       4       5

100    200    300   400   500

can someone please help me ?

Thanks!.

[#2]

Hi,

You can do this within the script using the generic load.

Check below example, but since it requires 3 parameters, I've added a KeyField to consider the whole data as one group (if you want you can drop it at the end).

T_Input:

LOAD 'static group' as KeyField, Key as Attribute, Field as Value Inline [

Field, Key

100, 1

200, 2

300, 3

400, 4

500, 5

];

GenTables:

Generic LOAD KeyField, Attribute, Value

Resident T_Input;

FinalTable:

LOAD Distinct KeyField Resident T_Input;

DROP Table T_Input;

for i = NoOfTables()-1 to 0 step -1

  let vTable = TableName($(i));

  if Left('$(vTable)', 10) = 'GenTables.' then

  left join (FinalTable)

  LOAD * Resident [$(vTable)];

  DROP Table [$(vTable)];

  end if

next

Hope this helps.

[#3]

thanks for reply.

i am not able to understand belfor loop next part.

can you please explain ?.

[#4]

Ok, after using the generic transformation, a number of tables is created.

Each one will be labeled as "GenTables.Attribute" (GenTables.1, GenTables.2, etc.) and will contain 2 fields, the KeyField and the Attribute.

The attribute (1, 2, 3, 4, 5) will have its corresponding value (100, 200, 300, 400, 500).

To visually understand what is happening in the generic load, add an "exit script" statement before the for loop part, reload your app and go to table viewer (ctrl+T).

The for... next part will be looping through these tables in the model to create one final table containing the KeyField and all the attributes.

The loop is done going backwards from total number of tables in model (NoOfTables()) to 0, to always give a correct table index to the TableName function (because after joining the attribute to the final table, we won't need the attribute table anymore so we drop it).

Your model may already contain other tables before applying this part, so we make sure to work on the tables created by the generic load, by checking if the table name contains the label assigned to them (here it is GenTables).

Hope that this clarifies how the process is executing.

I also suggest you check out this link: http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

[phaneendra_kunc]

Probably you can attempt to do that using "Transformation step". Hope this will be helpful.

I have added your data into excel and used the transformation step..

Data in Excel:

Field	Key
100	1
200	2
300	3
400	4
500	5

Script:

LOAD Key,

     [1.000000],

     [2.000000],

     [3.000000],

     [4.000000],

     [5.000000]

FROM

C:\Users\ip1858\Desktop\Test.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Rotate(left)

));

Cheers...Phani

[#6]

Phani,

thanks for your reply.

I need to rotate/transpose date from Other table - Resident Table.

does this below posible ?

Load

Key,filed Resident mytable (Rotate(left));

something like above ?

thanks in advance.

[#7]

Thanks for your reply.

It's really very helpful and it seems to be working fine.

I am getting below results.

5      4     3     2     1
500  400 300 200 100

I would like to get below results.

1      2    3      4    5

100 200 300 400 500

How can we change the order ?

Thanks!.

[#8]

You can specify an order by clause in the generic load, try like this:

GenTables:

Generic LOAD KeyField, Attribute, Value

Resident T_Input

Order By Attribute desc;

However, the order of fields in a table is not that important as you can manipulate this within your objects, charts and expressions.

Well, hope that helps anyways.

[#9]

thanks for your reply.

i am new to qlikview.

can you please assist me,

what change should i make in for Loop

to get column in above order ?

Thanks!.

[#10]

There's no need to change anything in the for loop.

Just check again my previous reply, and add the "order by Attribute desc" into the generic load part.

The columns will then be ordered the way you want it.