Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Previous Command


Hi there,

I am using the previous command, and it is working, however, It only looks at the previous line of data, as I would like it to look at the previous line of data, and if it dose not match the current line, move to the line above the previous line and check if that matches and so on and so forth.

Therefore I need the code to look at all previous lines until it finds a match instead of just looking at the one line above.

I have the code I am using below, and a sample of the data.

The sample shows a table of data with a list of product builders and their Login and Logout times.

TXNDATE with a Sign = 1 is a login time, and TXNDATE with a Sign = 3 is a logout time.

For each Logout time I need to check the previous lines of data to find the correct login time.

The Data is Loaded and sorted by the UnigueID and TXNDATE into a table named PBIII. The code I am using for the LogInTimeCalc is working when the TXNDATE I am looking for is on a previous, otherwise it fails.

The scenario that works for the snapshot is highlighted in green, and the secenario that is not working is highlighted in orange.

I understand why my code is not working as the previous command only looks at the line below, but I am hoping someone might be able to help amend the code so that it looks at all lines above the line I am looking at.

Code:

PBIII:

Load

     UniqueID,

     LogInID,

     FullName,

     Station,

     Sign,

     TNXDATE,

     LogInTime,

     LogOutTime,

     PRIVIOUS (LogInTime) as PriviousLogInTime,

     IF ( Sign = 1, Null(),

               IF ( UniquieID <> Previous(UniquieID), NULL(),

                         IF ( TNXDATE > Privious (TNXDATE), Privious (TNXDATE), NULL()))) as LogInTimeCalc

Please see table attached with sample data.

Any help would be much appriciated.

Thanks

Tags (2)
4 Replies
MVP & Luminary
MVP & Luminary

Re: Previous Command

Try this:

Input:

LOAD UniqueID,

    FULLNAME,

    Calendar_Date,

    LOGINID,

    STATION,

    SIGN,

    TXNDATE,

    LOGINTIME,

    LOGOUTTIME,

    LOGINTIMECALC

FROM

myexcelwb.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Result:

NoConcatenate

Load

UniqueID,

    FULLNAME,

    Calendar_Date,

    LOGINID,

    STATION,

    SIGN,

    TXNDATE,

    LOGINTIME,

    LOGOUTTIME,

    LOGINTIMECALC,

    if(UniqueID=Previous(UniqueID), PREVIOUS (LOGINTIME)) as PreviousLogInTime,

    IF ( SIGN = 1, Null(),

              IF ( UniqueID <> Previous(UniqueID), NULL(),

                        IF ( TXNDATE > Previous (TXNDATE), Previous (TXNDATE), NULL()))) as LogInTimeCalc

Resident Input

Order by UniqueID, TXNDATE;

drop table Input;   


talk is cheap, supply exceeds demand
Employee
Employee

Re: Previous Command

Hi,

try using lookup(...) instead of previous(...). You can use it to look up for the value you want. If it has not been loaded, null is returned.


Here's the extract from the documentation:


"lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.


Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).


The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.


Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned."


Regards,

Brice

Employee
Employee

Re: Previous Command

Question: in your sample data, the orange value have a different "UNIQUEID", is this normal?

By the way, you should probably not call it UNIQUEID if it is not unique, see the green values

Brice

Employee
Employee

Re: Previous Command

If UNIQUEID is the field used to link a login with a logout, here is a script you may use to obtain what you want. If not, you have to find a field or a concatenation of fields to do the linkage.

I was wrong about using "lookup(...)" as you want the closest previous value, not the first one.

I assume the lines in the Excel File are sorted correctly.

t:

LOAD

    RowNo()    as [Row ID],

    UniqueID,

    FULLNAME,

    Calendar_Date,

    LOGINID,

    STATION,

    SIGN,

    TXNDATE,

    LOGINTIME,

    LOGOUTTIME

    //LOGINTIMECALC

FROM

[Copy of Previous command Community Example.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

FOR i = 1 to NoOfRows('t')

    Trace i = $(i);

    LET currentUniqueID     = peek('UniqueID', i - 1, 'tmp');

    LET currentRowID        = peek('Row ID', i - 1, 'tmp');

    LET currentLogoutTime    = peek('LOGOUTTIME', i - 1, 'tmp');

   

    // If this line is a logout line, look for the corresponding login, else don't do anything

    IF len(trim(currentLogoutTime)) > 0 THEN

   

        TRACE Looking for login time for currentRowID = $(currentRowID), UniqueID = $(currentUniqueID);

   

        LET j = $(i) - 1;

        SET found = 0;

       

        TRACE j = $(j);

       

        // Start looking previous lines, until there is no line left or the login time has been found

        DO WHILE (j > 0 AND found = 0)

                       

            LET previousRowID         = peek('Row ID', j - 1, 'tmp');

            LET previousUniqueID     = peek('UniqueID', j - 1, 'tmp');

            LET previousLoginTime     = peek('LOGINTIME', j - 1, 'tmp');

           

            TRACE Testing row previousRowID = $(previousRowID), UniqueID = $(previousUniqueID);

           

            IF (previousUniqueID = currentUniqueID) THEN

                TRACE Login time found: $(previousLoginTime);

                   

                JOIN(t)

                LOAD

                    '$(currentRowID)'        as [Row ID],

                    '$(previousLoginTime)'    as LOGINTIMECALC

                AUTOGENERATE

                    1;

                   

                LET found = 1;   

            ENDIF;

           

            LET j = j - 1;

           

        LOOP;

    ENDIF;

NEXT i;