Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Generate missing Values

Hey guys,

I need your help with following issue.

I have a table like:

UserIDLocationIDDateLocationIDChanged
1201.01.2016
1202.01.20164
1203.01.2016
1204.01.2016
1205.01.201610
1206.01.2016

What I'm trying to achieve now is, I want to replace the LocationID Values with the LocationIDChanged.

So every Row with Date >= Date with LocationIDChanged and Date < Date with next LocationIDChanged should get the new LocationID.

So result should be:

UserIDLocationIDDateLocationIDChanged
1201.01.2016
1402.01.20164
1403.01.2016
1404.01.2016
11005.01.201610
11006.01.2016

Hope that makes it clear what i want.

Thank you for any help

1 Solution

Accepted Solutions
MVP
MVP

Re: Generate missing Values

Maybe like this:


LOAD *,
if(previous(UserID) <> UserID, LocationID, If(len(trim(LocationIDChanged)), LocationIDChanged, Peek(LocationIDNew))) as LocationIDNew;
LOAD UserID,
LocationID,
Date,
LocationIDChanged
FROM
[https://community.qlik.com/thread/204500]

View solution in original post

3 Replies
MVP
MVP

Re: Generate missing Values

Maybe like this:


LOAD *,
if(previous(UserID) <> UserID, LocationID, If(len(trim(LocationIDChanged)), LocationIDChanged, Peek(LocationIDNew))) as LocationIDNew;
LOAD UserID,
LocationID,
Date,
LocationIDChanged
FROM
[https://community.qlik.com/thread/204500]

View solution in original post

Re: Generate missing Values

Hi,

one solution might be:

QlikCommunity_Thread_204500_Pic1.JPG

table1:

LOAD UserID,

    Alt(Num#(LocationIDChanged),Peek(LocationID),LocationID) as LocationID,

    Date,

    LocationIDChanged

FROM [https://community.qlik.com/thread/204500] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Not applicable

Re: Generate missing Values

Thank you,

this works