Coverting data in proper format

[deepakqlikview_123]

Managers	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Daryl	12	23	10	9	24	13	19	11	15	21	10	12	179
Thomas	8	10	12	15	18	21	19	16	13	10	8	15	165
Martin	9	12	15	18	21	24	22	20	18	16	14	12	201

Hi All,

Can you please suggest how to convert above data in proper format so that I can create bar chart based on that.

Thanks

[MK_QSL · Accepted solution]

Table:

CrossTable(Month, Data,1)

Load * Inline

[

  Managers, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

  Daryl, 12, 23, 10, 9, 24, 13, 19, 11, 15, 21, 10, 12

  Thomas, 8, 10, 12, 15, 18, 21, 19, 16, 13, 10, 8, 15

  Martin, 9, 12, 15, 18, 21, 24, 22, 20, 18, 16, 14, 12

];

Now Create a Bar Chart

Dimension

Managers

Month

Expression

SUM(Data)

Go to Sort Tab

Select Month

Write below in expression =Date#(Month,'MMM')

now you can sort by Numeric Ascending or Descending Order

[jagan]

Hi Deepak,

Try CrossTable like below

CrossTable(Month, Value)

LOAD

*

FROM DataSource;

This converts the data in below format

Managers      Month     Value

Hope this helps you.

Regards,

Jagan.

[jyothish8807]

Hi Deepak,

Use cross table option for this using file wizad.Select Qualifier Name,Attribute name and Data Name as your requirment.After that you can create bar char  by taking Manager and Month as Dimension and Sum(data) as Exp.

Regadrs

KC

[deepakqlikview_123]

Hi Both It is working fine

Thanks

[MarcoWedel]

Table:
CrossTable(MonthText, Data,1)
Load * Inline
[
  Managers, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
  Daryl, 12, 23, 10, 9, 24, 13, 19, 11, 15, 21, 10, 12
  Thomas, 8, 10, 12, 15, 18, 21, 19, 16, 13, 10, 8, 15
  Martin, 9, 12, 15, 18, 21, 24, 22, 20, 18, 16, 14, 12
];

Left Join (Table)
LOAD Distinct
     MonthText,
     Month(Date#(MonthText,'MMM')) as Month
Resident Table;

DROP Field MonthText;

to let the Month field be dual in the first place

[jyothish8807]

PFA

[deepakqlikview_123]

Hi All,

My origional data in below format.

LOAD //[Subarea Department],
    // [Subarea Direct],
     [Subarea VP/Director],
[Subarea Director/Manager],
/*[Subarea Manager],
[Subarea Owner],
Subarea,
[Funding Type],
[Funding Portfolio],
[Funding Budget Level 2],
[Funding Budget Level 3],
[Project Owner Department],
[Project Owner Direct],
[Project Owner VP/Director],
[Project Owner Director/Manager],
[Project Owner Manager],
[Project Owner Subarea Owner],
[Project Owner Subarea],*/
[Resource Manager Name],
[Resource Manager ID],
[Resource Name],
[Resource ID],
[Employee Type],
Vendor,
Onshore,
/*[Role Category],
[Company Number],
[Department Number],
Facility, */
[Project ID],
[Project Name],
/* [Capitalization PCT],
[Project Class],
[Project Gate],
[Project Phase],
[Project Role], */
[Project Manager Name],
[Project Manager ID],
[Task Name],
[Task ID],
[Task Category],
//[Run Date],
     [Start Date],
Skills,
//Position,
     //[Temp Date (start)],
     [End Date],
[Month 1] AS Jan,
[Month 2] AS Feb,
[Month 3] AS Mar,
[Month 4] AS Apr,
[Month 5] AS May,
[Month 6] AS Jun,
[Month 7] AS Jul,
[Month 8] AS Aug,
[Month 9] AS Sep,
[Month 10] AS Oct,
[Month 11] AS Nov,
[Month 12] AS Dec
//[Month Totals],
from data source.

Can u plz suggest how to apply cross table for above data

[MK_QSL]

CrossTable(Month,Data,19)

Load * Form TableName;

[deepakqlikview_123]

Hi Manish,

But a m getting less number of rows the resulting rows should be origional rows*12.

Plz currect me if I am wrong.

Thanks