Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

MVP
MVP

Excel Data Reading

Hello,

i have an excel of the follwoing format:

DateDepartment ADateDepartment BDateDepartment C
1-1-131001-1-131112-2-12200
2-1-131103-1-1311212-1-13500

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

DateDepartmentAmount
1-1-13A100
2-1-13A110
1-1-13B111
3-1-13B112
2-2-12C200
12-1-13C500

How do i do that?

Thanks.

1 Solution

Accepted Solutions
sushil353
Honored Contributor II

Re: Excel Data Reading

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

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

View solution in original post

15 Replies
sushil353
Honored Contributor II

Re: Excel Data Reading

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

MVP
MVP

Re: Excel Data Reading

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.

Partner
Partner

Re: Excel Data Reading

Hi,

you have this standard Dep. or more.

find the attached file:

MVP
MVP

Re: Excel Data Reading

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
Honored Contributor II

Re: Excel Data Reading

hi,

Please find attached.

HTH

Sushil

sushil353
Honored Contributor II

Re: Excel Data Reading

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

right(@3,1) as Department

in the script.

MVP
MVP

Re: Excel Data Reading

is this app using the same excel as source?

Partner
Partner

Re: Excel Data Reading

Hi sushil kumar,

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

sushil353
Honored Contributor II

Re: Excel Data Reading

yes,

It is using same excel as source.