topic Re: Excel import problem in QlikView

Excel import problem

Fri, 20 Nov 2020 18:02:30 GMT

I am hoping that someone can help me.

First of all, I would say I am inexperienced and then for the main question.

Can I select different cells in excel to import as one category

The Basic Qlikview Model:

Date	Task	Hours Spent

All of the fields are imported correctly

However, when I export our company data to excel I get an order which is more like follow

Date	Task	Hours Spent	Reason	Task	Hours Spent	Reason

Tough the Task appears in different columns, it should practically be categorised under one item "TASK", but instead of importing "TASK", I get a Task and a Task1, same for reasone and hours spent.

PS. The data from the excel sheet is available on our SQL server, I do not know much about SQL or how to import SQL data

I have attached the excel sheet, please help!!

Thanks

Re: Excel import problem

giakoum — Tue, 04 Mar 2014 16:38:21 GMT

If task is in different columns, then it will appear in excel in different columns. But you can manipulate the export by using the attached macro or even better, create a hidden chart exactly the way you want it to be exported and export that one instead, using a button and an action.

SUB Excel

set obj = ActiveDocument.GetSheetObject( "CH01" )

w = obj.GetColumnCount

if obj.GetRowCount>1001 then

h=1000

else h=obj.GetRowCount

end if

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

objExcel.Worksheets(1).select()

objExcel.Visible = True

set CellMatrix = obj.GetCells2(0,0,w,h)

column = 1

for cc=0 to 8

objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text

objExcel.Cells(1,column).EntireRow.Font.Bold = True

column = column +1

c = 1

r =2

for RowIter=1 to h-1

for ColIter=0 to 8

objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text

'msgbox(CellMatrix(RowIter)(ColIter).Text)

c = c +1

r = r+1

c = 1

END SUB

Re: Excel import problem

Tue, 04 Mar 2014 16:56:28 GMT

Are there values I am supposed to edit from the macro in order for it to work? I have reloaded the data, and the Plants still seem to be imported as their own column / field instead of one... See screenshot attached of import

Re: Excel import problem

giakoum — Tue, 04 Mar 2014 17:00:26 GMT

this is merely an example, you need to adjust the macro to your needs...

Re: Excel import problem

Tue, 04 Mar 2014 17:11:42 GMT

Thanks, are there any links / videos on the qlikview site I can watch to learn about macros?

Re: Re: Excel import problem

giakoum — Tue, 04 Mar 2014 17:17:51 GMT

maybe, but macros is not a QlikView thing. It is knowing how to code in VB script. For QlikView related objects and methods see attached files.