Generate rows of incrementing dates in load script

[sifatnabil]

Hi all,

I am loading data where each row in the table indicates a login or logout for each user. The table looks like this:

username, action, date

timothy, login, 1/5/2014

timothy, logout, 4/5/2014

ben, login, 2/5/2014

ben, logout, 8/5/2014

I want to generate new rows in this table between a login and logout which will have the missing dates. So the final table in load script should look like this:

username, action, date

timothy, login, 1/5/2014

timothy, login, 2/5/2014

timothy, login, 3/5/2014

timothy, logout, 4/5/2014

ben, login, 2/5/2014

ben, login, 3/5/2014

ben, login, 4/5/2014

ben, login, 5/5/2014

ben, login, 6/5/2014

ben, login, 7/5/2014

ben, logout, 8/5/2014

I want to generate the underlined rows using a for or while loop.

What I've attempted so far is to load the original table, and then load a second table like below:

LOAD
upper(USER_NAME) as USER_NAME,
DATE-peek(DATE,-1,USLogin) as datecount
Resident USLogin
While IterNo() ? <--getting stuck here

But that's where I'm getting stuck. Essentially, for each username, I want to increment the date of a login until it reaches the date of a logout, and generate rows in between a login and logout with these incremented dates.

How can this be achieved? Thanks.

[sifatnabil]

Thanks!

[sifatnabil]

Hi Sergey, could this be modified such that if a logout date for a user does not exist, (i.e. they haven't logged out yet), it populates the dates to today's date?

e.g. if the raw data is

username, action, date

timothy, login, 12/6/2014

Output should be populated to current date (16th June):

username, action, date

timothy, login, 12/6/2014

timothy, login, 13/6/2014

timothy, login, 14/6/2014

timothy, login, 15/6/2014

timothy, login, 16/6/2014

Currently, if a logout date does not exist for a user, it just shows the login date. I tried editing the script to change the "DateTo" field like below, but it didn't work:

//LEFT JOIN (Data)//
tmp1Data:
LOAD
User&'|'&IF(Previous(Date)>Date OR isNull(Previous(Date)),Date,Previous(Date))  AS %KEY,
//AS Date,
IF(Previous(Date)>Date OR isNull(Previous(Date)),Date,Previous(Date)) AS DateFrom,
if(Action='logout',Date, today()) AS DateTo
Resident Data
WHERE Date<>IF(Previous(Date)>Date OR isNull(Previous(Date)),Date,Previous(Date)) AND Action='logout' 

Thanks!

[SergeyMak]

Hi Sifat,

Just add these lines after INLINE statement

tmp5Data:

LOAD

User,

Max(Date) AS MaxDate

Resident tmpData

Group by User;

LEFT Join (tmpData)

LOAD User,

MaxDate AS Date,

1 AS LastAction

Resident tmp5Data;

DROP Table tmp5Data;

Concatenate (tmpData)

LOAD User,

'logout' AS Action,

Date(Today()) AS Date

Resident tmpData

WHERE Action='login' AND LastAction=1;

PFA