Generate missing Values

[#1]

Hey guys,

I need your help with following issue.

I have a table like:

UserID	LocationID	Date	LocationIDChanged
1	2	01.01.2016
1	2	02.01.2016	4
1	2	03.01.2016
1	2	04.01.2016
1	2	05.01.2016	10
1	2	06.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:

UserID	LocationID	Date	LocationIDChanged
1	2	01.01.2016
1	4	02.01.2016	4
1	4	03.01.2016
1	4	04.01.2016
1	10	05.01.2016	10
1	10	06.01.2016

Hope that makes it clear what i want.

Thank you for any help

[swuehl · Accepted solution]

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]

[MarcoWedel]

Hi,

one solution might be:

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

[#4]

Thank you,

this works