Previous Command

[#1]

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

[Gysbert_Wassenaar]

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;    

[Brice-SACCUCCI]

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

[Brice-SACCUCCI]

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

[Brice-SACCUCCI]

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;