Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Excel load unmerge cells with new value

IDs Merge.bmp

Hi Guys

I am loading over 600 excel sheets into QV ( Its a data sorting project)

The issue is one of the columns Q ,[ Review Date],is merged ( randomly 3,2,7,8 lines)

See sample above

I am using either a Macro or Formula to unmerge and bring the correct values into the column,

This is the macro

Sub UnMergeA()

With Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

.MergeCells = False

With .SpecialCells(xlCellTypeBlanks)

.Value = .Value

.FormulaR1C1 = "=R[-1]C"

End With

End With

End Sub

It unmerges the cell and if the cell is blank looks to the cells above and takes the value of that cell and populates it in, if it is not blnak it retains value.

See Result below

Ids Unmerge.bmp

I dont relish doing this for 600 sheets before the load, is it possible to write this as part of the load code in QV?

Thanks

Anne

1 Solution

Accepted Solutions
MVP
MVP

Excel load unmerge cells with new value

Should be possible within the load filter, try something like

Directory;

LOAD Field1,

     Field2

FROM

Map1.xlsx

(ooxml, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

));

You could easily create the correct filter with the assistant, enable transformation step, check out FILL filter option tab.

Hope this helps,

Stefan

6 Replies
MVP
MVP

Excel load unmerge cells with new value

Should be possible within the load filter, try something like

Directory;

LOAD Field1,

     Field2

FROM

Map1.xlsx

(ooxml, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

));

You could easily create the correct filter with the assistant, enable transformation step, check out FILL filter option tab.

Hope this helps,

Stefan

Not applicable

Excel load unmerge cells with new value

Hi Swuehl,

Im getting the following error :

Bad Zip File
LOAD [Account Line],

  [Next Annual Review Date]

FROM

(ooxml, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

))

is it because i amended the script incorrectly ?

Any help is greatly appreciated

thanks,
Anne

MVP
MVP

Excel load unmerge cells with new value

ooxml format specifier is for Excel2010 ( .xlsx) files. If you are trying to open an older Excel / .xls file. I think biff is the format to specify.

LOAD [Account Line],

  [Next Annual Review Date]

FROM

(biff, embedded labels, table is Table1, filters(

Replace(2, top, StrCnd(null))

))

Check also that embedded labels and Table1 is appropriate for your file.

Hope this helps,

Stefan

Not applicable

Excel load unmerge cells with new value

Stefan

Thanks a mill

The Load is now completing,

But how to I build it within the table !

Not sure how to actually enable the filter !

Thanks so much

MVP
MVP

Excel load unmerge cells with new value

Not quite sure what you mean with enabling the filter?

The filter should be enabled if you state a filter like

..filters(Replace(2, top, StrCnd(null)..

in your format specification.

Maybe I missed something that we need to consider for your setting, the table wizard should help you to create the correct filter. Have you tried the wizard?

Not applicable

Excel load unmerge cells with new value

Stefan

Realised that I should have used the Wizard all along I got it going on now

Thanks a million for your help really appreciated

A