Number entries in field

[inescastelhano]

Dear all,

I am importing data from a text file in qlikview. My file will look as follows:

06/07/2015     10:42:40      Execution started

(...)                    (...)             (...)

(...)                    (...)             (...)

(...)                    (...)             (...)

06/07/2015      10:45:00     Execution failed

06/07/2015      10:47:00     Execution finished

if the execution fails,

and something like this:

06/07/2015     10:42:40      Execution started

(...)                    (...)             (...)

(...)                    (...)             (...)

(...)                    (...)             (...)

06/07/2015      10:45:00     something that doesn't matter

06/07/2015      10:47:00     Execution finished

if the execution succeeds.

I am loading the data from the text file, but only these 3 rows:

TMP:

LOAD @1 as Date,

     time(left(@2,8)) as Hour,

     @3 as Message

from

[...]

My aim is to number, or label, each row so that I can say "if row 2 = 'execution failed', store message (and do something), else discard message".

I tried RowNo but i doesn't work.

Any ideas?

Thanks in advance

[maxgro · Accepted solution]

TMP:

LOAD @1 as Date,

     time(left(@2,8)) as Hour,

     @3 as Message,

    rowno() as id

from

[...]

do you want to check the status of the execution (failed or ok)?

if yes try

    if(wildmatch(@3, '*failed*'), 1,0) as FlagFailed,

[inescastelhano]

Nice!

I didn't know this function, it's great

However, rowno still does not work.

Now I have something like this:

Date               Hour          Message                    Status

06/07/2015     10:42:40      Execution started          ok

(...)                    (...)             (...)                            ok

(...)                    (...)             (...)                              ok    

(...)                    (...)             (...)                             ok    

06/07/2015      10:45:00     Execution failed              failed

06/07/2015      10:47:00     Execution finished             ok

And I would like to have one single Flag for each module (I have several modules). If there is one message with flag failed, the module failed. If all of them are ok, the module is ok. Something like:

Module      Status (if flag=1, failed, ok)     Started          Finished

A                    failed                              min(Hour)         max(Hour)

B                     ok                                  min(Hour)     max(Hour)

C                    ok                                   min(Hour)     max(Hour)

[maxgro]

Now I have something like this:

Date               Hour          Message                    Status

06/07/2015     10:42:40      Execution started          ok

(...)                    (...)             (...)                            ok

(...)                    (...)             (...)                              ok   

(...)                    (...)             (...)                             ok   

06/07/2015      10:45:00     Execution failed              failed

06/07/2015      10:47:00     Execution finished             ok

And I would like to have one single Flag for each module (I have several modules).

where is module in your data?

[inescastelhano]

Module is a Column I created when I imported the data. I have several tables, and each table is a diferent module.

LOAD @1 as Date,

     time(left(@2,8)) as Hour,

     @3 as Message,

     'Module1' as Module,

     if(wildmatch(@3, '*failed*'), 1,0) as FlagFailed

FROM

[...table1]

LOAD @1 as Date,

     time(left(@2,8)) as Hour,

     @3 as Message,

     'Module2' as Module,

     if(wildmatch(@3, '*failed*'), 1,0) as FlagFailed

FROM

[...table2]

Thanks

[maxgro]

if you want one single flag for each module you can use max(FlagFailed) grouping by module

if 1 --> failed

if 0 --> ok

load Module, max(FlagFailed)

Resident sometable

group by Module; 

sometable should be the table with Date, Hour, Message, Module, FlagFailed