Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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
];
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.