Doubts about Cross Table

[#1]

Hi Friends,
for following example how to create the cross tables.

Product ID | Product Name|Jan-12|Feb-12|Mach-12| Jan-13|

1                |  TV               | 40      | 23     |  50        |  25   |

Expected Result:

Product ID | Product Name| Year1  | sales|        Year2   | sales|

   1             |  TV               |  Jan-12|  40    |         Jan- 13|  25  |

Thank you,

VP

[jagan · Accepted solution]

Hi Vijay,

Try like this

Temp:

CrossTable(Months, Value, 2)

LOAD

*

FROM DataSource;

Data:

LOAD

*,

Year(Date) AS Year;

LOAD

*,

Date(Date#(Months, 'MMM-YY')) AS Date

RESIDENT Temp;

DROP TABLE Temp;

Now in Pivot Table

Dimension: Product ID, Product Name, Year, Months

Expression : Sum(Value)

Now drag (Pivot) Year and Month column to the top of the table.

Regards,

jagan.

[#3]

But in the given scenario the months are given in a row and doesnt content any coloumn name so while loading how should i load it

[sasikanth]

HI Vijay

Try some thing like below

CrossTable(year,sales,2)
LOAD * Inline [
Product ID , Product Name,Jan-12,Feb-12,Mach-12, Jan-13,
1    ,  TV, 40 ,3,50,  25

];

[jagan]

Hi,

That is why we are using the CrossTable(), which unpivots (Rows to Columns) the data.  Please check for CrossTable example in Qlikview help file or community.

Regards,

Jagan.