Forum Discussion

PranavChoudhary's avatar
PranavChoudhary
Qrew Trainee
3 years ago

De-Duplicate Data in a Text Field.

I am bringing data into a Formula-Text field from another table using formula query function.
Now, data has entries separated by ";" and I want only distinct entries to be displayed. For Example : ABC;DEF;DEF;GHI;DEF;ABC are the entries and I want only ABC;DEF;GHI as output.
My data has more than 20 entries so bringing it in as multi-select text doesn't fits.
If I Split the entries on basis of ; and collects them in Multi-Select Text and use summary field to display it in parent table then problem is solved but then again Multi-Select Text allows only 20 entries and I have more than that so Summary field only summarise from those 20.
Please advise some way. Thank you.
------------------------------
Pranav Choudhary
------------------------------
  • The only method I know if brute force.  I think that this example from my cheat note was to turn it into a vertical list but you could probably adapt this formula to not have the breaks to a new line  \n.

    If you do decide to tackle this as a longer brute force formula can you post your final solution?

    var text RawList = [My semi delimited text field];


    var text A = Trim(Part($RawList,1,";"));

    var text B = If(not Contains($A,Trim(Part($RawList,2,";"))), List("\n", $A, Trim(Part($RawList,2,";"))), $A);

    var text C = If(not Contains($B,Trim(Part($RawList,3,";"))), List("\n", $B, Trim(Part($RawList,3,";"))), $B);

    var text D = If(not Contains($C,Trim(Part($RawList,4,";"))), List("\n", $C, Trim(Part($RawList,4,";"))), $C);

    var text E = If(not Contains($D,Trim(Part($RawList,5,";"))), List("\n", $D, Trim(Part($RawList,5,";"))), $D);

    var text F = If(not Contains($E,Trim(Part($RawList,6,";"))), List("\n", $E, Trim(Part($RawList,6,";"))), $E);

    var text G = If(not Contains($F,Trim(Part($RawList,7,";"))), List("\n", $F, Trim(Part($RawList,7,";"))), $F);

    var text H = If(not Contains($G,Trim(Part($RawList,8,";"))), List("\n", $G, Trim(Part($RawList,8,";"))), $G);

    var text I = If(not Contains($H,Trim(Part($RawList,9,";"))), List("\n", $H, Trim(Part($RawList,9,";"))), $H);

    var text J = If(not Contains($I,Trim(Part($RawList,10,";"))), List("\n", $I, Trim(Part($RawList,10,";"))), $I);

    var text K = If(not Contains($J,Trim(Part($RawList,11,";"))), List("\n", $J, Trim(Part($RawList,11,";"))), $J);

    var text L = If(not Contains($K,Trim(Part($RawList,12,";"))), List("\n", $K, Trim(Part($RawList,12,";"))), $K);

    var text M = If(not Contains($L,Trim(Part($RawList,13,";"))), List("\n", $L, Trim(Part($RawList,13,";"))), $L);

    var text N = If(not Contains($M,Trim(Part($RawList,14,";"))), List("\n", $M, Trim(Part($RawList,14,";"))), $M);

    var text O = If(not Contains($N,Trim(Part($RawList,15,";"))), List("\n", $N, Trim(Part($RawList,15,";"))), $N);

    var text P = If(not Contains($O,Trim(Part($RawList,16,";"))), List("\n", $O, Trim(Part($RawList,16,";"))), $O);

    var text Q = If(not Contains($P,Trim(Part($RawList,17,";"))), List("\n", $P, Trim(Part($RawList,17,";"))), $P);

    var text R = If(not Contains($Q,Trim(Part($RawList,18,";"))), List("\n", $Q, Trim(Part($RawList,18, ";"))), $Q);

    var text S = If(not Contains($R,Trim(Part($RawList,19,";"))), List("\n", $R, Trim(Part($RawList,19,";"))), $R);

    var text T = If(not Contains($S,Trim(Part($RawList,20,";"))), List("\n", $S, Trim(Part($RawList,20,";"))), $S);

    var text U = If(not Contains($T,Trim(Part($RawList,21,";"))), List("\n", $T, Trim(Part($RawList,21,";"))), $T);

    var text V = If(not Contains($U,Trim(Part($RawList,22,";"))), List("\n", $U, Trim(Part($RawList,22,";"))), $U);

    var text W = If(not Contains($V,Trim(Part($RawList,23,";"))), List("\n", $V, Trim(Part($RawList,23,";"))), $V);

    var text X = If(not Contains($W,Trim(Part($RawList,24,";"))), List("\n", $W, Trim(Part($RawList,24,";"))), $W);

    var text Y = If(not Contains($X,Trim(Part($RawList,25,";"))), List("\n", $X, Trim(Part($RawList,25,";"))), $X);

    var text Z = If(not Contains($Y,Trim(Part($RawList,26,";"))), List("\n", $Y, Trim(Part($RawList,26,";"))), $Y);

    $Z

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
  • The only method I know if brute force.  I think that this example from my cheat note was to turn it into a vertical list but you could probably adapt this formula to not have the breaks to a new line  \n.

    If you do decide to tackle this as a longer brute force formula can you post your final solution?

    var text RawList = [My semi delimited text field];


    var text A = Trim(Part($RawList,1,";"));

    var text B = If(not Contains($A,Trim(Part($RawList,2,";"))), List("\n", $A, Trim(Part($RawList,2,";"))), $A);

    var text C = If(not Contains($B,Trim(Part($RawList,3,";"))), List("\n", $B, Trim(Part($RawList,3,";"))), $B);

    var text D = If(not Contains($C,Trim(Part($RawList,4,";"))), List("\n", $C, Trim(Part($RawList,4,";"))), $C);

    var text E = If(not Contains($D,Trim(Part($RawList,5,";"))), List("\n", $D, Trim(Part($RawList,5,";"))), $D);

    var text F = If(not Contains($E,Trim(Part($RawList,6,";"))), List("\n", $E, Trim(Part($RawList,6,";"))), $E);

    var text G = If(not Contains($F,Trim(Part($RawList,7,";"))), List("\n", $F, Trim(Part($RawList,7,";"))), $F);

    var text H = If(not Contains($G,Trim(Part($RawList,8,";"))), List("\n", $G, Trim(Part($RawList,8,";"))), $G);

    var text I = If(not Contains($H,Trim(Part($RawList,9,";"))), List("\n", $H, Trim(Part($RawList,9,";"))), $H);

    var text J = If(not Contains($I,Trim(Part($RawList,10,";"))), List("\n", $I, Trim(Part($RawList,10,";"))), $I);

    var text K = If(not Contains($J,Trim(Part($RawList,11,";"))), List("\n", $J, Trim(Part($RawList,11,";"))), $J);

    var text L = If(not Contains($K,Trim(Part($RawList,12,";"))), List("\n", $K, Trim(Part($RawList,12,";"))), $K);

    var text M = If(not Contains($L,Trim(Part($RawList,13,";"))), List("\n", $L, Trim(Part($RawList,13,";"))), $L);

    var text N = If(not Contains($M,Trim(Part($RawList,14,";"))), List("\n", $M, Trim(Part($RawList,14,";"))), $M);

    var text O = If(not Contains($N,Trim(Part($RawList,15,";"))), List("\n", $N, Trim(Part($RawList,15,";"))), $N);

    var text P = If(not Contains($O,Trim(Part($RawList,16,";"))), List("\n", $O, Trim(Part($RawList,16,";"))), $O);

    var text Q = If(not Contains($P,Trim(Part($RawList,17,";"))), List("\n", $P, Trim(Part($RawList,17,";"))), $P);

    var text R = If(not Contains($Q,Trim(Part($RawList,18,";"))), List("\n", $Q, Trim(Part($RawList,18, ";"))), $Q);

    var text S = If(not Contains($R,Trim(Part($RawList,19,";"))), List("\n", $R, Trim(Part($RawList,19,";"))), $R);

    var text T = If(not Contains($S,Trim(Part($RawList,20,";"))), List("\n", $S, Trim(Part($RawList,20,";"))), $S);

    var text U = If(not Contains($T,Trim(Part($RawList,21,";"))), List("\n", $T, Trim(Part($RawList,21,";"))), $T);

    var text V = If(not Contains($U,Trim(Part($RawList,22,";"))), List("\n", $U, Trim(Part($RawList,22,";"))), $U);

    var text W = If(not Contains($V,Trim(Part($RawList,23,";"))), List("\n", $V, Trim(Part($RawList,23,";"))), $V);

    var text X = If(not Contains($W,Trim(Part($RawList,24,";"))), List("\n", $W, Trim(Part($RawList,24,";"))), $W);

    var text Y = If(not Contains($X,Trim(Part($RawList,25,";"))), List("\n", $X, Trim(Part($RawList,25,";"))), $X);

    var text Z = If(not Contains($Y,Trim(Part($RawList,26,";"))), List("\n", $Y, Trim(Part($RawList,26,";"))), $Y);

    $Z

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • PranavChoudhary's avatar
      PranavChoudhary
      Qrew Trainee
      Thanks Mark. It was really helpful.

      ------------------------------
      Pranav Choudhary
      ------------------------------