topic Re: Coverting data in proper format in QlikView

Coverting data in proper format

deepakqlikview_123 — Fri, 19 Sep 2014 07:59:53 GMT

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

Re: Coverting data in proper format

MK_QSL — Fri, 19 Sep 2014 08:04:06 GMT

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

Re: Coverting data in proper format

jagan — Fri, 19 Sep 2014 08:12:53 GMT

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.

Re: Coverting data in proper format

jyothish8807 — Fri, 19 Sep 2014 08:15:34 GMT

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

Re: Coverting data in proper format

deepakqlikview_123 — Fri, 19 Sep 2014 08:15:51 GMT

Hi Both It is working fine

Thanks

Re: Coverting data in proper format

MarcoWedel — Fri, 19 Sep 2014 08:16:26 GMT

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

Re: Re: Coverting data in proper format

jyothish8807 — Fri, 19 Sep 2014 08:28:41 GMT

PFA

Re: Coverting data in proper format

deepakqlikview_123 — Fri, 19 Sep 2014 11:54:03 GMT

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

Re: Coverting data in proper format

MK_QSL — Fri, 19 Sep 2014 11:55:54 GMT

CrossTable(Month,Data,19)

Load * Form TableName;

Re: Coverting data in proper format

deepakqlikview_123 — Fri, 19 Sep 2014 12:15:01 GMT

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

Re: Coverting data in proper format

MK_QSL — Fri, 19 Sep 2014 12:15:36 GMT

Provide me sample data ...

Re: Coverting data in proper format

MK_QSL — Fri, 19 Sep 2014 12:19:04 GMT

Could be because all null values would be removed..

Re: Coverting data in proper format

deepakqlikview_123 — Fri, 19 Sep 2014 12:23:37 GMT

There are aroung 65000 rows so it was excepting to get 65000

*12 rows, but I am getting around 1,11000 rows.But when I checked table structure after creation of cross table values were assigned properly.(By using Ctrl T).

Is this right or wrong plz suggest.

Thanks

Re: Coverting data in proper format

MK_QSL — Fri, 19 Sep 2014 12:41:59 GMT

Make sure that your Months (jan, feb, march....) dont have null values...

Re: Coverting data in proper format

deepakqlikview_123 — Fri, 19 Sep 2014 12:55:37 GMT

is there are null values what would have to be done.

Re: Coverting data in proper format

MK_QSL — Fri, 19 Sep 2014 13:03:02 GMT

Deepak, can you provide me sample data of around 5-10 lines...?

Re: Coverting data in proper format

jyothish8807 — Sun, 21 Sep 2014 10:02:32 GMT

Try using file wizard for creating cross table.It will read all the data within your sheet.

Regards