topic Re: Excel Data Reading in QlikView

Excel Data Reading

tresB — Fri, 17 May 2013 09:10:34 GMT

Hello,

i have an excel of the follwoing format:

Date	Department A	Date	Department B	Date	Department C
1-1-13	100	1-1-13	111	2-2-12	200
2-1-13	110	3-1-13	112	12-1-13	500

Now i need to read this into qlikview to get out put as below:

Date	Department	Amount
1-1-13	A	100
2-1-13	A	110
1-1-13	B	111
3-1-13	B	112
2-2-12	C	200
12-1-13	C	500

How do i do that?

Thanks.

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 09:27:11 GMT

Hi,

you can use the utiltity in the edit script "Enable transformation" which you will get when you do next when you select the table from the edit script..

t1:

LOAD Date(@1,'MM-DD-YYYY') as Date,

@2 as Department

FROM

tableformat.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Unwrap(Col, Pos(Top, 3)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Date')))

));

Below is the attached xl file and appliction.

HTH

Sushil

Re: Excel Data Reading

tresB — Fri, 17 May 2013 09:39:30 GMT

thanks Sushil for your response. but what i wanted is a bit different. i wanted one new column as 'Amount' to be generated and the department names to be there under the department field(please refer the above post).

Thanks.

Re: Excel Data Reading

kumarnatarajan — Fri, 17 May 2013 10:03:00 GMT

Hi,

you have this standard Dep. or more.

find the attached file:

Re: Excel Data Reading

tresB — Fri, 17 May 2013 10:08:25 GMT

Kumar,

this is so manual! i am just trying to avoid this. Something automatic and dynamic would be required, because my excel book would contain multiple sheets and many more departments(it may increase dynamically). Anyway, thank you for your input.

Thanks.

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 10:18:02 GMT

hi,

Please find attached.

HTH

Sushil

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 10:20:11 GMT

to get the only A,B, C from the excel then use

right(@3,1) as Department

in the script.

Re: Excel Data Reading

tresB — Fri, 17 May 2013 10:30:14 GMT

is this app using the same excel as source?

Re: Excel Data Reading

kumarnatarajan — Fri, 17 May 2013 10:32:03 GMT

Hi sushil kumar,

i don't know fully about "Enable Transformation Step" . so where i can get good tutorialspoint about this

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 10:43:25 GMT

yes,

It is using same excel as source.

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 10:44:57 GMT

Hi Kumar,

I don't have any tutorial for Enable Transformation Step..

Just got idea by experience only..

Regards,

Sushil

Re: Excel Data Reading

tresB — Fri, 17 May 2013 11:03:32 GMT

it seems that your latest app uses different excel.

right(@3,1) as Department??

Thanks.

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 11:08:00 GMT

Put the below code in the QV file..

t1:

LOAD Date(@1,'MM-DD-YYYY') as Date,

num(@2) as Amount,

right(@3,1) as Department

FROM

tableformat.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Unwrap(Col, Pos(Top, 3)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Replace(3, left, StrCnd(null)),

Replace(3, top, StrCnd(start, 'D', not)),

Remove(Row, RowCnd(Compound,

RowCnd(CellValue, 1, StrCnd(equal, 'Date')),

RowCnd(Interval, Pos(Top, 2), Pos(Top, 1), Select(1, 0))

)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Date')))

));

Regards,

Sushil

Re: Excel Data Reading

tresB — Fri, 17 May 2013 11:16:24 GMT

thanks Sushil for your effort and help. now i have to explore with the transformation how you achieved the same.

Re: Excel Data Reading

sushil353 — Fri, 17 May 2013 11:18:44 GMT

if need any help then keep posting..

Re: Excel Data Reading

kumarnatarajan — Fri, 17 May 2013 11:33:29 GMT

Hi,

try this one. This is dynamic.

T1:
CrossTable(particular,Data,2)
LOAD RowNo() as No,
@1&'-'&Previous(@1) as new,
@2,
@3
FROM
Data2.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Transpose(),
Transpose(),
Transpose(),
Rotate(right),
Transpose()
));

MapTab:
Mapping LOAD particular&'-'&new as map,Data as Datamap Resident T1;

NoConcatenate LOAD SubField(new,'-',1) as Department,Data as Amount,
Date(ApplyMap('MapTab',particular&'-Date-'&SubField(new,'-',1),0)) as Date
Resident T1 Where ApplyMap('MapTab',particular&'-Date-'&SubField(new,'-',1),0)>0;
DROP Table T1;

find the attached file.