topic tricky xls file to transform it in something readable by qlickview in QlikView

tricky xls file to transform it in something readable by qlickview

hectorgarcia — Tue, 24 Nov 2009 20:40:31 GMT

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

tricky xls file to transform it in something readable by qlickview

Tue, 24 Nov 2009 21:17:20 GMT

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

tricky xls file to transform it in something readable by qlickview

hectorgarcia — Tue, 24 Nov 2009 21:32:20 GMT

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

tricky xls file to transform it in something readable by qlickview

hector — Tue, 24 Nov 2009 21:37:18 GMT

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

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

rgds

tricky xls file to transform it in something readable by qlickview

Tue, 24 Nov 2009 21:55:06 GMT

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

tricky xls file to transform it in something readable by qlickview

Tue, 24 Nov 2009 21:55:53 GMT

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

tricky xls file to transform it in something readable by qlickview

hectorgarcia — Tue, 24 Nov 2009 22:44:14 GMT

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

))

;