Forum Discussion

walkerline117's avatar
walkerline117
Contributor
8 years ago

Transformation for data

Hi… We have below requirment: Original data from data soure(e.g. database) Here’s what we need to transform to: Appreciate any kind of suggestions. Thanks Han
sample_pipeline
del's avatar
del
Contributor III
8 years ago

There’s got to be an easier way, but I succeeded with a Mapper containing the following expression and then a Splitter following the Mapper.

$Errors.replace(/\d+. /g,“|”).split(“|”).map(x => {“Name”:$Name,“FMNO”:$FMNO,“Phone #1”:$[‘Phone #1’],“Phone #2”:$[‘Phone #2’],“Email”:$Email,“Error”:x}).filter((v,k)=>v.Error!=“”)

del's avatar
del
Contributor III
8 years ago

LOL, I challenged myself to make it better. It’s not prettier, but you’re not stuck to hard-coding column names.

$Errors.replace(/ *\d+. /g,“|”).split(“|”).map(x => $.mapValues((v1,k1)=>k1 == “Errors” ? x : v1).mapKeys((v2,k2)=>k2 == “Errors” ? “Error” : k2)).splice(1)

Surely, someone can improve on this even…

  • walkerline117's avatar
    walkerline117
    Contributor
    8 years ago

    Thanks so much, it really helps.

    The only thing is that the result comes into different groups by ppl name.
    How to flat the result so everything goes into one structure(no group)

    Thanks

    • del's avatar
      del
      Contributor III
      8 years ago

      Adding a splitter snap after the mapper flattened the data for me.

      Also, there’s a dozen ways to do this I think. The expression I provided keeps the pipeline short, but it’s hard to interpret if you need to debug. You can simplify the expression and use additional snaps. Attached is a pipeline with just two possible options that achieve the same results.

      community-1794_2017_12_14.slp (10.7 KB)