topic Re: Generate rows of incrementing dates in load script in QlikView

Generate rows of incrementing dates in load script

sifatnabil — Fri, 09 May 2014 03:08:06 GMT

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.

Re: Generate rows of incrementing dates in load script

tresB — Fri, 09 May 2014 04:40:33 GMT

Input:

Load * Inline [
username, action, date

timothy, login, 1/5/2014

timothy, logout, 4/5/2014

ben, login, 2/5/2014

ben, logout, 8/5/2014
];

Join
Temp:
Load
username,
Max(date) as MaxDate
Resident Input Group By username;

Final:
Load
username,
If(date+IterNo()-1=MaxDate, action, 'Login') as action,
date(date+IterNo()-1) as date

Resident Input while date+IterNo()-1<=MaxDate and action <>'logout'; // corrected

Drop table Input;

Re: Generate rows of incrementing dates in load script

anbu1984 — Fri, 09 May 2014 05:24:24 GMT

Small correction to Tresesco's code which generates extra row on max date with login as action

Input:

Load * Inline [

username, action, date

timothy, login, 1/5/2014

timothy, logout, 4/5/2014

ben, login, 2/5/2014

ben, logout, 8/5/2014

];

Join

Temp:

Load

username,

Max(date) as MaxDate

Resident Input Group By username;

Final:

Load

username,

If(date+IterNo()-1=MaxDate, action, 'Login') as action,

date(date+IterNo()-1) as date

Resident Input while date+IterNo()-1<MaxDate Or (IterNo() = 1 And date=MaxDate);

Drop table Input;

Re: Generate rows of incrementing dates in load script

tresB — Fri, 09 May 2014 06:07:59 GMT

Good catch anbu. The easier correction(as I did) would be : and action <>'logout' to avoid the max date load from resident table.

Re: Re: Generate rows of incrementing dates in load script

SergeyMak — Fri, 09 May 2014 08:29:22 GMT

Did you try to add several intervals?

For example

timothy, login, 3/4/2014

timothy, logout, 4/4/2014

ben, login, 2/4/2014

ben, logout, 8/4/2014

timothy, login, 1/5/2014

timothy, logout, 4/5/2014

ben, login, 2/5/2014

ben, logout, 8/5/2014

As I see MaxDate will not work.

So. I tried to solve this situation with intervalmatch.

PFA

Re: Generate rows of incrementing dates in load script

anbu1984 — Fri, 09 May 2014 08:47:23 GMT

Sergey, You attached the wrong app

Re: Re: Generate rows of incrementing dates in load script

SergeyMak — Fri, 09 May 2014 08:48:53 GMT

sorry

PFA

Re: Generate rows of incrementing dates in load script

sifatnabil — Fri, 09 May 2014 13:11:42 GMT

Hi Sergey, many thanks for your program. This is almost working - however, it's generating rows for the days that each user is not logged in, which is undesirable because the data has 1000+ users. How would you modify it such that it only generates rows for when the user is logged in? Thanks.

Re: Generate rows of incrementing dates in load script

sifatnabil — Fri, 09 May 2014 13:57:57 GMT

When it's loading, it works fine if I load just 5 or 6 users, however, loading hundreds of users causes 10 million+ lines to be generated and the load doesn't stop. Might there be something wrong with the joins?

Re: Re: Generate rows of incrementing dates in load script

SergeyMak — Mon, 12 May 2014 00:53:14 GMT

Hi,

I modified the script

Now only login actions are repeating.

PFA

Regards,

Sergey

Re: Generate rows of incrementing dates in load script

sifatnabil — Tue, 13 May 2014 04:40:48 GMT

Thanks!

Re: Generate rows of incrementing dates in load script

sifatnabil — Mon, 16 Jun 2014 03:14:33 GMT

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!

Re: Re: Generate rows of incrementing dates in load script

SergeyMak — Mon, 16 Jun 2014 08:12:11 GMT

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