extract using delimiters

[viditverma62]

I have a data : ABC|001|BCE|2021 - Video-view-impressions-abc

i want to extract the data for first 4 delimiters pipes , i want to ignore after the hyphon

[viditverma62]

Hello , thanks for helping.

as per my requirement. in my data there are campaign names with that contains data up to 5 pipes and after that 5th pipe there is year also for example 

SMO|LOC|001|PUR|CHERNOBYL NEW|2021 - video.01 - spend.22 - impr 

if i found 5 pipes and year 2021 or 2020 , i would like to have this in my output that mean year as well.

SMO|LOC|001|TUN|Locked|2021

and data can also be like this as well where there is no year

SMO|LOC|001|PUR|Locked|video011-impr-0922-spend-costy-hto

so on basis of 5 pipes here i want data like

SMO|LOC|001|TUN|Locked

i want this to be one columns

[viditverma62]

Hello , thanks for helping.

as per my requirement. in my data there are campaign names with that contains data up to 5 pipes and after that 5th pipe there is year also for example 

SMO|LOC|001|PUR|CHERNOBYL NEW|2021 - video.01 - spend.22 - impr 

if i found 5 pipes and year 2021 or 2020 , i would like to have this in my output that mean year as well.

SMO|LOC|001|TUN|Locked|2021

and data can also be like this as well where there is no year

SMO|LOC|001|PUR|Locked|video011-impr-0922-spend-costy-hto

so on basis of 5 pipes here i want data like

SMO|LOC|001|TUN|Locked

i want this to be display in one seperate column

[apathetichell]

corrected

[viditverma62]

Thank you so much

can you please bit elaborate what you did exactly

i am unable to comrehend. First, in field 1 you split into rows and then in field 1 again you split to columns ? may i know the reason why? and can you please explain why you used that formula and group by? that would be of great help

[apathetichell]

Hi!

So first we need to split to rows with | as a delimiter... Why rows? Well we don't know how many | we have. We have a record ID so we can make sure we don't intermingle records.

Next -  we know we want to split the LAST | by "_"... We do a second split - this time isolating the data that comes after "-".

Next - we need to create a dummy column name ("data") to allow for crosstab.

the we crosstab with "|" added as a delimiter and set to concatenate. Group by record ID means that your data stays consistent with the original records.

Hope that works!

[viditverma62]

Thank youuuu so much for this big HELPP. 

[viditverma62]

hi,

Sorry for double tap

i checked and run the workflow its running fine but i am finding that

there are some records where - is not there and hence its throwing that data as well. for example

SMO|ERP|EMD|WB Movies|Its A Sin|UES_HBO_A+_REM|SUB|BMM

in this example there is no - and hence its also showing the data after 5th pipe as well because the taxonomy is not consistent

Can you please help.

[apathetichell]

Sure - but I'm unclear in a situation like you mentioned above - how would you want it to appear?

Can you mock up some input/output examples.

[viditverma62]

My Raw data :

ABC|FRV|005|TUN|Summer and King|2021 - Video Views - Premiere & Ep 2 - 1.25 - 1.31

ABC|JAN|TON|WBS Movies|Its A Boy|UES_HBO_A+_REM|HBOSUB|BMM

DEF|MAR|003|TUN|Ghost of UP|2021| / 5.3-5.11|Non-Skippable 15s

STO|AAP|002|TUN|American Drama|2020 : xyzdce:video views

My output data(which i want)

ABC|FRV|005|TUN|Summer and King|2021

ABC|JAN|TON|WBS Movies|Its A Boy

DEF|MAR|003|TUN|Ghost of UP|2021

STO|AAP|002|TUN|American Drama|2020

Basically, i want by text to columns or by advance formula :

Search for first 5 pipes, if found year 2020 or 2021 then include year (6th pipe as well) like this mentioned below:

ABC|FRV|005|TUN|Summer and King|2021

if no year (2020 and 2021) found in string then only take the first 5 pipe data i.e:

ABC|JAN|TON|WBS Movies|Its A Boy

[apathetichell]

This works on your sample data.

New strategy creates and index and searches for index values (ie pipe number) below 6 - or if pipe 6, replaces with year if available or ignores.