Display multiple fields on to X axes

[shumailh]

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.

[Oleg_Troyansky · Accepted solution]

You need to use Crosstable load - it's part of the "Text Files" load wizard. You will have 2 qualifying fields - Category and Product.

[shumailh]

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

[syed_muzammil]

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.

[shumailh]

I never use this option before, this is amazing... Thanks for the help Oleg and Muzammil.

[shumailh]

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

[shumailh]

I got the simple solution

=MonthName(Date(left(Fin_MnYr,5)))