topic Transform Rows into Column in QlikView

Transform Rows into Column

vikasmahajan — Fri, 11 May 2012 12:53:43 GMT

I have following table

Item no Dimension Dimension Value

FDABT001 BRAND ABZ Group

FDABT001 DEPT Domestic Marketing

FDABT001 THERSEG Anti-Helmintic

I want following Output (Cross Table)

ITem no BRAND Dept THERSEG

=============================================

FDABT001 abz group Domestic Marketing Anti-Helmintic

Can any one help me for getting this output.

Regards

Vikas MAhajan

Transform Rows into Column

Jason_Michaelides — Fri, 11 May 2012 13:01:46 GMT

Can't test this at the moment but try creating a pivot table with two dimensions (ItemNo and Dimension) and a single expression Only(DimensionValue). Then drag the "Dimension" dimension up to the top.

Hope this helps,

Jason

Transform Rows into Column

vikasmahajan — Sat, 12 May 2012 06:58:20 GMT

I WANT THIS IN SQL SERVER

Transform Rows into Column

Jason_Michaelides — Sat, 12 May 2012 07:34:05 GMT

So you're asking how to do something in SQL, not QlikView? Sorry, can't help - maybe try a SQL forum instead?

Transform Rows into Column

adnan_rafiq — Sat, 12 May 2012 08:03:48 GMT

I am not sure about SQL but you can do it in Qlikview by it very native functionality of Cross Table

See Help in Qlikview help and search Cross Table you will find syntax for that.

Transform Rows into Column

vikasmahajan — Sat, 12 May 2012 08:42:15 GMT

Can we achive by loading data from tables insted of excel

Pls reply

Transform Rows into Column

adnan_rafiq — Sat, 12 May 2012 08:56:09 GMT

Yes you can for sure. it can be applied to any data source which QV support.

If by any reason it don't work, then Store table in a QVD first and then apply cross table.

Regards

Transform Rows into Column

Jason_Michaelides — Sat, 12 May 2012 09:04:01 GMT

I think the opposite of Crosstable() is needed here. Crosstable() is used to create tables in the format you already have.

Transform Rows into Column

adnan_rafiq — Sat, 12 May 2012 09:20:24 GMT

Yes Jason you are right. I guess I mixed it up.

Then What is see in this problem is two step aaproach

Load separate table for each value.

Eg.

in this case

i will use,

load

Item no ,

Dimension Value as Brand

from table where Dimension = 'BRAND'

join

load

Item no ,

Dimension Value as DEPT

from table where Dimension = 'DEPT'

and so on...

It will work in SQL as well as in Qlikview and give you desired result

Transform Rows into Column

Jason_Michaelides — Sat, 12 May 2012 10:00:10 GMT

Maybe. I think some more detail on the requirements is needed - what does I WANT THIS IN SQL SERVER mean?

Re: Transform Rows into Column

Sat, 12 May 2012 11:17:45 GMT

Hello

You need a GENERIC LOAD most probably.

Take a look at attached file

Hope this helps.

Transform Rows into Column

vikasmahajan — Sat, 12 May 2012 12:05:59 GMT

Hello

I Worked in Qlikview scripting perfet

Thanks to all for resolving my problem

Vikas

Transform Rows into Column

vikasmahajan — Sat, 12 May 2012 12:07:09 GMT

I mean i want to this is sql scripts