Filling in blanks, based on next value

NathanDorsch
NathanDorsch
in Magic ETL

I have a dataset in which a User ID will be identified during a session.... and then later change User IDs... and in between, there are rows of data in which the User ID is not identified - but I want to fill in those gaps with the next User ID that is identified during that session...

Row -- Session ID -- User ID -- Timestamp

001 -- 123 -- Null -- 10000...

002 -- 123 -- Null -- 10002...

003 -- 123 -- ABC -- 10004...

004 -- 987 -- Null -- 10006...

005 -- 123 -- Null -- 10005...

006 -- 123 -- DEF -- 10007....

007 -- 987 -- XYZ -- 10009...

I would want User ID value in rows 001 and 002 populated with 'ABC'

I would want User ID value in row 005 populated with 'DEF'

I would want User ID value in row 004 populated with 'XYZ'

Can anyone advise on how I can do this?

Answers

  • lvtrujillot
    lvtrujillot 
    SELECT  *,
CASE
   WHEN `userID` IS NULL  
   THEN (SELECT DISTINCT `userID` FROM `your_table` WHERE (`sessionID`=T.`sessionID`) AND (`userID` IS NOT NULL)) ELSE `userID` END AS "new_UserID"
FROM `your_table` T

    If I understood correctly, this should work.

    Hope it helps!

Categories