topic Re: cross table in QlikView

cross table

prabhas277 — Wed, 23 Dec 2015 10:23:15 GMT

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

Re: cross table

Wed, 23 Dec 2015 10:30:12 GMT

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

Re: cross table

Chanty4u — Wed, 23 Dec 2015 10:45:47 GMT

hi,

pfa

Re: cross table

sunny_talwar — Wed, 23 Dec 2015 10:59:16 GMT

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;

Re: cross table

Chanty4u — Wed, 23 Dec 2015 11:01:53 GMT

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

Re: cross table

sunny_talwar — Wed, 23 Dec 2015 11:02:49 GMT

Yes my friend?

Re: cross table

Chanty4u — Wed, 23 Dec 2015 11:06:08 GMT

nothng bro..ur awesome simply

Re: cross table

sunny_talwar — Wed, 23 Dec 2015 11:15:51 GMT

Thank you

Re: cross table

effinty2112 — Wed, 23 Dec 2015 14:38:25 GMT

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