Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

question about formatting my excel data

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.

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: question about formatting my excel data

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!

5 Replies

Re: question about formatting my excel data

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)))

));

bertdijks
New Contributor III

Re: question about formatting my excel data

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))
))
));

Not applicable

Re: question about formatting my excel data

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.

Not applicable

Re: question about formatting my excel data

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!

Not applicable

Re: question about formatting my excel data

YES! perfect thank you both for your input.

Community Browser