topic Re: How to convert column into row in qlikview sql ? in QlikView

How to convert column into row in qlikview sql ?

Sat, 21 Jun 2014 23:35:49 GMT

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!.

Re: How to convert column into row in qlikview sql ?

Sun, 22 Jun 2014 02:04:43 GMT

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

Hope this helps.

Re: How to convert column into row in qlikview sql ?

Sun, 22 Jun 2014 02:34:08 GMT

thanks for reply.

i am not able to understand belfor loop next part.

can you please explain ?.

Re: How to convert column into row in qlikview sql ?

Sun, 22 Jun 2014 03:32:32 GMT

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

Re: How to convert column into row in qlikview sql ?

phaneendra_kunc — Sun, 22 Jun 2014 04:15:54 GMT

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

Re: How to convert column into row in qlikview sql ?

Sun, 22 Jun 2014 16:51:09 GMT

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.

Re: How to convert column into row in qlikview sql ?

Mon, 23 Jun 2014 00:56:26 GMT

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!.

Re: How to convert column into row in qlikview sql ?

Mon, 23 Jun 2014 01:15:01 GMT

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.

Re: How to convert column into row in qlikview sql ?

Mon, 23 Jun 2014 02:10:08 GMT

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!.

Re: How to convert column into row in qlikview sql ?

Mon, 23 Jun 2014 04:42:01 GMT

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.

Re: Re: How to convert column into row in qlikview sql ?

Mon, 23 Jun 2014 04:59:00 GMT

This is all done by Transformation step..There is more option available to modify the table..

after clicking next there is a option of cross table which allow you to transform row to column.