Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below grid in excel and i am loading it into my qlikview document. Also consider that i cannot change the format.
Category | Product | Jan-09 | Feb-09 | Mar-09 | Apr-09 | May-09 | Jun-09 |
Category 1 | P1 | 3.71 | 3.85 | 3.996871 | 4.14 | 4.283742 | 4.43 |
Category 2 | P1 | 2.68 | 2.38 | 2.080332 | 1.78 | 1.480664 | 1.18 |
Category 3 | P2 | 0.07 | 0.08 | 0.0804 | 0.09 | 0.0908 | 0.10 |
Category 4 | P2 | 1.46 | 1.30 | 1.48 | 1.4398 | 1.45 | 1.52 |
Category 5 | P3 | 0.53 | 0.41 | 0.57 | 0.53 | 0.53 | 0.54 |
Category 6 | P3 | 14.3 | 14.54 | 13.89 | 14.3 | 13.43 | 13.06 |
Category 7 | P4 | 8.16 | 7.31 | 7.51 | 8.16 | 7.62 | 7.05 |
Below is the code to load:
Business_Detail:
LOAD Category, Product,
[Jan-10] as Fee_Jan10,
[Feb-10] as Fee_Feb10,
[Mar-10] as Fee_Mar10,
[Apr-10] as Fee_Apr10,
[May-10] as Fee_May10,
[Jun-10] as Fee_Jun10,
[YTD Jun-10] as YTD_2010
FROM
[Business1.xls]
I want to make a line chart for the above grid where i want months to be on X axes. please help.
I try to transpose the data but it couldn't work with the two descriptive fields.
You need to use Crosstable load - it's part of the "Text Files" load wizard. You will have 2 qualifying fields - Category and Product.
You need to use Crosstable load - it's part of the "Text Files" load wizard. You will have 2 qualifying fields - Category and Product.
Hi Oleg,
Which text file load wizard you are telling? I tried "table files" and tried all the options while transformation but not working. & how can i load excel file using text file load wizard?
Regards,
Shumail
Hi,
When you load the Excel file using table wizard and press next then you can see the transform button . Press next again and you can see the CrossTable button on th top right corner.
I never use this option before, this is amazing... Thanks for the help Oleg and Muzammil.
Hi Oleg / Muzammil,
Now, one more issue I have when I use the cross table the date fields which were crossed is now showing as in number format . see the snapshot. Any solution for this?
Regards,
Shumail Hussain
I got the simple solution
=MonthName(Date(left(Fin_MnYr,5)))