Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

What is it with the ... data-reformatting-wizard in QlikView?


Hi,

most of my apps are based on Excel sheets. Simple enough. The wizard makes it very easy to clean the data from blank lines which are in Excel often used for clarity, using either the line_nr or some condition on the cells'  contents or to transform tables.

Now, the conditional deleting of rows from the base data is somewhat tricky - I often have trouble with it:

=> In my current base file, e.g. I want to delete the lines with the text "Ist Summe"/ "Soll Summe"/ "Delta Trainings" - I cannot make that for some reason - every time I run through the wizard and look at the outcome, those rows are still in...

Can someone help me here?

Maybe there is something I'm generally doing wrong with the wizard - though it's kind of hard to get it wrong...

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
MVP
MVP

Re: What is it with the ... data-reformatting-wizard in QlikView?

Have you tried just a WHERE clause?

LOAD Aufgaben as Mitarbeiter,

     [Verwaltung / Führung FM Abt.],

     [Tasks Einteilung

und Priorisieren],

     [Task und Schichtberichte],

     [Bestellungen SAP],

     [Kennlinien erstellen],

     FFZ,

     Staplerfahrer,

     Hebebühne,

     [3+1 Schulung],

     [MS Projekt],

     Aenis,

     [Q - Pluse],

     QlikView,

     [Tiket - System],

     Energiewerte,

     [Reparatur Anlagen],

     [Anlagenwartung

(Konservierung, Härteofen)],

     [Wartungen

(Haus und Gebäudetechnik)],

     [Reparatur Kleingeräte],

     Elektroarbeiten,

     Metallarbeiten,

     Schweißarbeiten,

     Malerarbeiten,

     Sprinklerwartung,

     Reinigung,

     Helferarbeiten,

     Gesamt

FROM

[Q_matrix_FM.xls]

(biff, embedded labels, table is [Sheet1$])

WHERE isnum(Aufgaben);

4 Replies
MVP
MVP

Re: What is it with the ... data-reformatting-wizard in QlikView?

Have you tried just a WHERE clause?

LOAD Aufgaben as Mitarbeiter,

     [Verwaltung / Führung FM Abt.],

     [Tasks Einteilung

und Priorisieren],

     [Task und Schichtberichte],

     [Bestellungen SAP],

     [Kennlinien erstellen],

     FFZ,

     Staplerfahrer,

     Hebebühne,

     [3+1 Schulung],

     [MS Projekt],

     Aenis,

     [Q - Pluse],

     QlikView,

     [Tiket - System],

     Energiewerte,

     [Reparatur Anlagen],

     [Anlagenwartung

(Konservierung, Härteofen)],

     [Wartungen

(Haus und Gebäudetechnik)],

     [Reparatur Kleingeräte],

     Elektroarbeiten,

     Metallarbeiten,

     Schweißarbeiten,

     Malerarbeiten,

     Sprinklerwartung,

     Reinigung,

     Helferarbeiten,

     Gesamt

FROM

[Q_matrix_FM.xls]

(biff, embedded labels, table is [Sheet1$])

WHERE isnum(Aufgaben);

rustyfishbones
Honored Contributor II

Re: What is it with the ... data-reformatting-wizard in QlikView?

When you import the Excel File you must use the enable transformation step

you then use a conditional delete, so the steps are

Click Next on this tab

2013-10-10_1036.png

Click Enable Tranformation step

2013-10-10_1037.png

Click the Button Conditional Delete

2013-10-10_1037_002.png

Choose Column 1 | Choose Contains | and type the text you dont want to load and click add, do this for the 3

lines

2013-10-10_1038.png

The result at the end of your load statement should look like this

2013-10-10_1039.png

datanibbler
Esteemed Contributor

Re: What is it with the ... data-reformatting-wizard in QlikView?

Hi,

@ Alan

I know the steps - only the outcome isn't as expected ;-)

@ swuehl

I can try - but why ISNUM()? - Oh, yeah, those numbers - there are names in that column, I just could not post those, so I used numbers. Still, I can try a WHERE() clause.

Thanks a lot!

Best regards,

DataNibbler

datanibbler
Esteemed Contributor

Re: What is it with the ... data-reformatting-wizard in QlikView?


Hi,

a WHERE()  clause works.

Solved.

Community Browser