Help with Pivot Table

[Nemo1]

Hello everyone

So my problem is the following. 

I have a dataset, where I have a column called "Month" and another column called Sales. 

I need to display the different months in QlikSense as columns. To explain myself better, I need for every month, a column. How can I do that?

thanks 

[Nemo1]

Hello, 

and once I do that, how should I continue?

For more clarity, this is the table that I have:

this is what i am aiming for: 

thanks in advance!

[Nemo1]

Yeah, it is not working for me. 

To give a clearer idea, this is my input table:

and this is what i need:

thanks in advance 🙂

[Fabiano_Martino_Intelco]

 @Nemo1 ,

I created the table you need with the following code:

>>>>>>>>>>

Data:

Load * Inline [

Date, Production Cost, Rent, Manpower, Total Cost, Sales, Margin

    1998, 65000, 4000, 80000, 149000, 240000, 91000

1999, 65000, 4000, 80000, 149000, 240000, 91000

    2000, 62000, 4000, 80000, 146000, 267000, 121000

    2001, 62000, 5000, 80000, 147000, 267000, 120000

    2002, 65000, 5000, 85000, 155000, 267000, 112000

    2003, 54000, 5000, 85000, 144000, 267000, 123000

    2004, 54000, 5000, 90000, 149000, 267000, 118000

    2005, 54000, 5000, 90000, 149000, 301000, 152000

]

;

DataUnpivot:

Crosstable(Element, Value)

Load *

Resident Data

;

DataTransposed:

Generic

Load Date, Element, Value

Resident DataUnpivot

;

Drop Table DataUnpivot;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

  Let vTableName = TableName($(vTableNo)) ;

  If Subfield(vTableName,'.',1)='DataTransposed' Then

    Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

  End If

Next vTableNo

Trace $(vListOfTables);

FinalTable:

Load distinct Date Resident Data;

For each vTableName in $(vListOfTables)

  Left Join (FinalTable) Load * Resident [$(vTableName)];

  Drop Table [$(vTableName)];

Next vTableName

Drop Table Data;

<<<<<<<<<<

Regards

Fabiano

[Fabiano_Martino_Intelco]

Hi @Nemo1 

sorry I made a mistake in the previous code.

This is the correct one:

>>>>>>>>>>

Data:

Load * Inline [

Date, Production Cost, Rent, Manpower, Total Cost, Sales, Margin

    1998, 65000, 4000, 80000, 149000, 240000, 91000

1999, 65000, 4000, 80000, 149000, 240000, 91000

    2000, 62000, 4000, 80000, 146000, 267000, 121000

    2001, 62000, 5000, 80000, 147000, 267000, 120000

    2002, 65000, 5000, 85000, 155000, 267000, 112000

    2003, 54000, 5000, 85000, 144000, 267000, 123000

    2004, 54000, 5000, 90000, 149000, 267000, 118000

    2005, 54000, 5000, 90000, 149000, 301000, 152000

]

;

DataUnpivot:

Crosstable(Element, Value)

Load *

Resident Data

;

DataTransposed:

Generic

Load Element, Date, Value

Resident DataUnpivot

;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

  Let vTableName = TableName($(vTableNo)) ;

  If Subfield(vTableName,'.',1)='DataTransposed' Then

    Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

  End If

Next vTableNo

Trace $(vListOfTables);

FinalTable:

Load distinct Element Resident DataUnpivot;

For each vTableName in $(vListOfTables)

  Left Join (FinalTable) Load * Resident [$(vTableName)];

  Drop Table [$(vTableName)];

Next vTableName

Drop Table Data;

Drop Table DataUnpivot;

<<<<<<<<<<

Regards

Fabiano

[Nemo1]

thanks so much for your answer and sorry for taking so long to get back to you. 

the problem is, the data I gave in the table below is just a sample, the reality is, that I have a huge dataset, and it would take me ages to type the data from this dataset once again.

there is no way to make this process more direct? without the need to type every number in the INLINE?? 

thanks

[Fabiano_Martino_Intelco · Accepted solution]

Hi @Nemo1 

I used the INLINE statement in my code to give you a self-contained example.

If you have your dataset in another format (Excel file, database tables, ...) you should modify the code in order to load your dataset from your specific source.

Regards

Fabiano

[Nemo1]

thank u so much!!