Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

vikasmahajan
Honored Contributor III

Transform Rows into Column

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

1 Solution

Accepted Solutions
adnan_rafiq
Contributor II

Transform Rows into Column

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

12 Replies
jason_michaelid
Honored Contributor II

Transform Rows into Column

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

Highlighted
vikasmahajan
Honored Contributor III

Transform Rows into Column

I WANT THIS IN SQL SERVER

jason_michaelid
Honored Contributor II

Transform Rows into Column

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

adnan_rafiq
Contributor II

Transform Rows into Column

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.

vikasmahajan
Honored Contributor III

Transform Rows into Column

Can we achive by loading data from tables insted of excel

Pls reply

adnan_rafiq
Contributor II

Transform Rows into Column

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

jason_michaelid
Honored Contributor II

Transform Rows into Column

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

adnan_rafiq
Contributor II

Transform Rows into Column

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

jason_michaelid
Honored Contributor II

Transform Rows into Column

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