tricky xls file to transform it in something readable by qlickview

[hectorgarcia]

i'm receiving a daily file with approx. 2000 new records in the format i show in the graphic. I'm trying to have access to the database but it will take some time and meanwhile i need to work with the file attached.

The file is coming in a 'pivot table format' but is not a pivot table. so many cells are coming empty and I need to populate this empty cells with the previoius value .

I tried several qlikview functions (crosstable, transform wizard, scripting inter records with no sucess.

Any help will be appreciated

using

[#2]

You can use the populate function in Excel wizard, this should work properly ?

[hectorgarcia]

how it works? I searched thet function in my 2007 excel and could not find it, i made a google research about with no direct results, thanks for your help

[hector]

hi, have you tried previous() function? something like

if(isnull(Field),previous(Field),Field) as Field2

rgds

[#5 · Accepted solution]

Use Qlikview Wizard to open Excel file

Then push the Transform button, then you can add values in the columns when missing data (up, down, left, right populate features are available)

Or have a look at Qlikview tutorial in the Wizard parts..

Rgds,

Sébastien

[#6]

Try the file wizard in QlikView - select "Table Files" then you comes right to the File Wizard.

At step 2, you will find a button called "Enable Transformation Step". In there you will be able to fill empty rows.

/Martin

[hectorgarcia]

it worked......thanks, seems that i had and issue with my 8.5 SR3, I downloaded the SR6 and iy worked as you explain

Thanks a lot for all the partners that replied!!!!!

LOAD

Year

,

Quarter

,

Month

,

Week

,

Day

,

[Timesheet Resource List Level 02]

,

[Timesheet Project List Level 02]

,

[Timesheet Task List Level 02]

,

[Timesheet Class]

,

[Actual Work Billable]

,

[Planned Work]

,

Year1

,

Quarter1

,

Month1

,

Day1

,

[Timesheet Resource List Level 021]

,

[Timesheet Project List Level 021]

FROM

[..\timesheet project server.xls] (biff, header is line, embedded labels, table is Sheet1$, filters

(

ColXtr

(1, RowCnd(Every), 0

),

Replace

(12, top, StrCnd(null

)),

ColXtr

(2, RowCnd(Every), 0

),

Replace

(13, top, StrCnd(null

)),

ColXtr

(3, RowCnd(Every), 0

),

Replace

(14, top, StrCnd(null

)),

ColXtr

(5, RowCnd(Every), 0

),

Replace

(15, top, StrCnd(null

)),

ColXtr

(6, RowCnd(Every), 0

),

Replace

(16, top, StrCnd(null

)),

ColXtr

(7, RowCnd(Every), 0

),

Replace

(17, top, StrCnd(null

))

))

;