Data set manipulation

Database
N/A

Data set manipulation

Hi all

am trying to get solution to the following problem. I have the table below and should add ape. to the  the values proceeding Damen.mode and the two proceeding values to ADS.

VisitID   Position               Text

A             1                             Storefront

A             2                             basket

A             3                             Damen.mode  

A             4                             PE

B             5                             ADS

B             6                             PE

B             7                             PE

B             8                             Storefront

Result should look like this

VisitID   Position               Text

A             1                             Storefront

A             2                             basket

A             3                             Damen.mode  

A             4                             ape.Damen.mode

B             5                             ADS

B             6                             ape.ADS

B             7                             ape.ADS

B             8                             Storefront

Thank you all.

1 REPLY
Junior Contributor

Re: Data set manipulation

You didn't tell the actual logic why it's only for those rows.

Looks like it's replace PE with the previous non-PE value

CASE
WHEN text_ = 'PE'
THEN 'ape.' || LAST_VALUE(CASE WHEN text_ = 'PE' THEN NULL ELSE text_ END IGNORE NULLS)
OVER(--partition by ??
ORDER BY Position_)
ELSE text_
END