topic Re: Generate missing Values in QlikView

Generate missing Values

Fri, 05 Feb 2016 16:36:54 GMT

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

Re: Generate missing Values

swuehl — Fri, 05 Feb 2016 19:25:11 GMT

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]

Re: Generate missing Values

MarcoWedel — Sat, 06 Feb 2016 01:50:09 GMT

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

Re: Generate missing Values

Mon, 08 Feb 2016 08:59:49 GMT

Thank you,

this works