cross table

[prabhas277]

Hi  experts,

please find the below attached file which is not in good format by using cross table how we can get in below format

   s1        cmpid     begin    s2         s3       ->This will be names of column

budget   102         k1        2014    aug   

budget   103         k2        2014     aug  

budget   104         k3        2014    aug  

budget   105         k4        2014     aug                 

[#2]

CrossTable(cmpid,test,4)
LOAD F1 as s1,
F2 as s2,
F3 as s3,
F4 as begin ,
[102],
[103],
[104],
[105]
FROM
C:\Users\MM64324\Desktop\tt.xlsx
(ooxml, embedded labels, table is Sheet1);

drop Field test;

it works

[Chanty4u]

hi,

pfa

[sunny_talwar]

May be this:

Table:

CrossTable (cmpid, Value, 4)

LOAD F1 as s1,

    F2 as s2,

    F3 as s3,

    F4 as begin,

    [102],

    [103],

    [104],

    [105]

FROM

text.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD s1,

  MonthName(MakeDate(s2, Month(Date#(Capitalize(s3), 'MMM')))) as MonthYear,

  s2 as Year,

  Month(Date#(Capitalize(s3), 'MMM')) as Month,

  Num#(cmpid, '##') as cmpid,

  Value

Resident Table;

DROP Table Table;

[Chanty4u]

sunny.................

[sunny_talwar]

Yes my friend?

[Chanty4u]

nothng bro..ur awesome simply

[sunny_talwar]

Thank you

[effinty2112]

Hi Prabhas,

CrossTable(cmpid, Data, 4)

LOAD F1 as s1,

     F2 as s2,

     F3 as s3,

     F4 as begin,

     [102],

     [103],

     [104],

     [105]

FROM

text.xlsx

(ooxml, embedded labels, table is Sheet1);

gives:

s1	cmpid	begin	s2	s3	Data
budget	102	k1	2014	aug	1
budget	102	k2	2014	aug	2
budget	102	k3	2014	aug	3
budget	102	k4	2014	aug	4
budjet	102	k5	2014	aug	5
budget	103	k1	2014	aug	5
budget	103	k2	2014	aug	6
budget	103	k3	2014	aug	7
budget	103	k4	2014	aug	4
budjet	103	k5	2014	aug	8
budget	104	k1	2014	aug	11
budget	104	k2	2014	aug	12
budget	104	k3	2014	aug	13
budget	104	k4	2014	aug	14
budjet	104	k5	2014	aug	15
budget	105	k1	2014	aug	16
budget	105	k2	2014	aug	17
budget	105	k3	2014	aug	18
budget	105	k4	2014	aug	19
budjet	105	k5	2014	aug	20