topic Re: Concatenating strings based on previous row values in Machine Learning

Concatenating strings based on previous row values

shaz — Thu, 23 Dec 2021 21:12:31 GMT

Consider the following input:

ID          PrevID
-------- ---------
33          NULL
272       33
317        272
318        317

I need to somehow get the following result:

Result
--------
/33
/33/272
/33/272/317
/33/272/317/318

I need to do this in SQL and if REALLY not possible, then anything else goes, and Databricks does not support recursive CTE's so I am out of ideas, i have an SQL Fiddle here that illustrates the recursivce CTE way that doesn't work on databricks: http://sqlfiddle.com/#!18/101df/3/0

Any help is appreciated, thanks

Re: Concatenating strings based on previous row values

shaz — Fri, 24 Dec 2021 13:03:38 GMT

Hi @Kaniz Fatma , Thanks for following up ! Great to meet you too

Re: Concatenating strings based on previous row values

Pholo — Sat, 08 Jan 2022 16:49:37 GMT

Hi @Shazal Gomes ,

I found out using pyspark this solution

from pyspark.sql import functions as F
from pyspark.sql.window import Window
from pyspark.sql import Row
from pyspark.sql.types import *
 
 
 
ids = ["33", "272", "317", "318"]
df_ids = spark.createDataFrame([Row(ID = i) for i in ids])
 
display(
  df_ids.withColumn(
    'PREVIOUS_ID', F.concat_ws('/',F.array_sort(F.collect_set(F.col('ID')).over(Window.orderBy(F.col('ID').cast('integer'))).cast('array<integer>')))
  )
)

Let me know if It meets your need.

Re: Concatenating strings based on previous row values

shaz — Mon, 10 Jan 2022 19:18:59 GMT

Hi, thanks for this code, it works as needed ! really would've liked to have it in SQL but this will do for now! Thank you

Re: Concatenating strings based on previous row values

sri — Sat, 12 Feb 2022 09:28:18 GMT

https://speakerdeck.com/mitasingh https://telegra.ph/Free-Printable-cover-letter-template-01-17 https://www.imdb.com/user/ur148564602/?ref_=nv_usr_prof_2 https://www.glenewinestate.com.au/profile/coverletter50/profile https://www.wpanet.org/profile/coverletter50/profile https://www.clarinetu.com/profile/coverletter50/profile https://www.digital.hms.org.il/profile/coverletter50/profile https://www.gn2.poli.ufrj.br/profile/coverletter50/profile https://www.figma.com/@mitasingh https://kuula.co/profile/mitasingh https://participation.lillemetropole.fr/profiles/mitasingh/activity https://coactuemtest.ub.edu/profiles/mitasingh/activity https://questions.parlement-ouvert.fr/profiles/mitasingh/activity?locale=en https://otakumode.com/mitasingh https://linkworld.us/profile/mitasingh http://82808.homepagemodules.de/u458_mitasingh.html http://bcmoney-mobiletv.com/mitasingh

Re: Concatenating strings based on previous row values

Kari_05152023 — Wed, 28 Jun 2023 14:29:28 GMT

Hi Kaniz:

I am reaching out for assistance using SQL/T-SQL for a string function to use on a concatenated string (i.e., DATABRICKS concatenated functionality is used to create unique identification names in the data table). I have read-only access and need to validate huge data sets. Thank you in advance!

Kind regards - Karen