topic Re: question about formatting my excel data in QlikView

question about formatting my excel data

Tue, 22 Apr 2014 12:57:02 GMT

Hello,

I have a problem with my datasource.

I want to import data from an excel dat i get delivered from my clients, this excel is an export from some program.

I do not have the possibility to change the export so i want to format it while loading the tables.

As you can see in the attachment, sheet: Problem. It is some strange form of reporting.

I have tried formatting the data by using a For loop to create a correct table.

sadly this did not work.

In the sheet: Result is shown how i would like to see the data imported.

Does anyone know how i can perform this?

I appreciate any answer.

Re: question about formatting my excel data

alexandros17 — Tue, 22 Apr 2014 13:03:08 GMT

Look at this syntax, I never used it directly but I know it works ...

Hope it helps

LOAD [Server Name],

Date,

Time,

User,

Program,

[Security Audit Log message text]

FROM

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

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

Re: question about formatting my excel data

bertdijks — Tue, 22 Apr 2014 13:16:47 GMT

This should do the trick:

This code is generated by the import wizard --> transformatiion steps:

Problemdata:

LOAD Artikel as Date,
    Quantity,
    [Price/each],
    F8 As Artikel
FROM
[Problem excel.xls]
(biff, embedded labels, header is 1 lines, table is Problem$, filters(
ColXtr(1, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(contain, '0', not)),
RowCnd(CellValue, 1, StrCnd(start, 'Artikel', not))
), 0),
Replace(8, top, StrCnd(null)),
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(contain, '0', not)),
RowCnd(CellValue, 1, StrCnd(contain, 'Artikel', not))
))
));

Re: question about formatting my excel data

Tue, 22 Apr 2014 14:16:11 GMT

Hi Bert,

This seems to do it almost in the way i would want it. But now i miss some data, it only imports 4 rows of my whole dataset.

Re: question about formatting my excel data

Tue, 22 Apr 2014 14:42:05 GMT

Got a different script from the wizard than Bert's -

LOAD Artikel as Date,

Quantity,

[Price/each],

F8 as Artikel

FROM

(biff, embedded labels, header is 1 lines, table is Problem$, filters(

ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

Replace(8, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Total'))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Totaal'))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Overall Total')))

));

HTH!

Re: question about formatting my excel data

Tue, 22 Apr 2014 14:46:49 GMT

YES! perfect thank you both for your input.