Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

5 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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
Partner - Contributor III
Partner - Contributor III

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
Author

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
Author

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
Author

YES! perfect thank you both for your input.