Excel Data Reading

[tresesco]

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.

[sushil353]

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)),

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

[tresesco]

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.

[kumarnatarajan]

Hi,

you have this standard Dep. or more.

find the attached file:

[tresesco]

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.

[sushil353]

hi,

Please find attached.

HTH

Sushil

[sushil353]

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

right(@3,1) as Department

in the script.

[tresesco]

is this app using the same excel as source?

[kumarnatarajan]

Hi sushil kumar,

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

[sushil353]

yes,

It is using same excel as source.