Select spezific rows in a table and count these

[#1]

Hello,

first sorry for my bad english, but i hope you can understand what i want to do.

i have imported an Excle-Table into qlikview.

In this table are a about 4000 rows. Each row is filled with information to one single batch-job like name, start stop time, exit code and so on.

what i want to do is, to select every row with exit code 1 or a string that included 'ended not ok'

Then I want to display these information in a table with two columns -> Name of the Task and the quantity of this task with exit code 1.

Now i realized this with a second table-import and a SQL-Statement with a count-function and group by clause.

This is the Statment to filter the rows i want to show

LOAD

     Name

     Count(Status)

FROM ...

WHERE Status = 'ended_not_ok'

GROUP BY Name

Is there any way to define some rules in the diagramm wizard, so that i dont have to import the same excel-sheet again?

[#2]

did nobody have a idea?

[#3]

The load statement works.

Now i load one excel sheet two times...first without any filter and selection of columns like this here.

     LOAD

          Name

          Status

          Time

          ...

     FROM excelsheet

and then with this

     LOAD

          Name

          Count(Status)

     FROM excelsheet

     WHERE Status = 'ended_not_ok'

     GROUP BY Name;

i want to creat the secound table out of the first load statement with the diagram wizard...is this possible??

[Gysbert_Wassenaar · Accepted solution]

No idea. I don't even know any diagram wizard. But it sounds like you want to do a resident load. You'll have to give the first load a table name and use the resident keyword with the table name instead of the from clause:

T1:

LOAD

     Name

     Status

     Time

...

FROM excelsheet;

T2:

LOAD

          Name

          Count(Status)

RESIDENT T1

     WHERE Status = 'ended_not_ok'

     GROUP BY Name;

[#5]

That works good. Thanks a lot.