Key field

[priyarane]

hi Community,

I wanted to create a key based on some fileds like below.

EX:

Data:

Parent   Child1   Child2   Child3   Child4         Key - We need to create like below

12-CX   D1-39   4R-8Y   Y5-DF                     Y5

6I-0I      TT-4I    65-p9    34-FR     58-Oi        58

7R-UP   6T-UU                                               6T

Key will come from last field and need to remove '-'

can some one pls help me how to create key.

[sunny_talwar]

May be like this:

SubField(Alt(Child4, Child3, Child2, Child1), '-', 1) as Key

[sunny_talwar · Accepted solution]

Or this if Child column won't be true nulls

SubField(If(Len(Trim(Child4)) > 0, Child4,

               If(Len(Trim(Child3)) > 0, Child3,

               If(Len(Trim(Child2)) > 0, Child2, Child1))), '-', 1) as Key

[swuehl]

Here is another option:

LOAD * ,

Subfield(Subfield(Trim(Child1 &' '&Child2 &' '&Child3 &' '&Child4),' ',-1),'-',1) as Key2;

LOAD * INLINE [

Parent,   Child1,   Child2 ,  Child3 ,  Child4,         Key - We need to create like below

12-CX ,  D1-39,   4R-8Y,   Y5-DF,   ,                  Y5

6I-0I ,     TT-4I ,   65-p9 ,   34-FR  ,   58-Oi ,       58

7R-UP ,  6T-UU                       ,,,,                        6T

];

[Anonymous]

Hi Priya,

Try this:

SubField(

    SUBFIELD(Parent

            &If(Len(Child1)>0,'#'&Child1)

            &If(Len(Child2)>0,'#'&Child2)

            &If(Len(Child3)>0,'#'&Child3)

            &If(Len(Child4)>0,'#'&Child4),'#',

    SubStringCount(

        Parent

            &If(Len(Child1)>0,'#'&Child1)

            &If(Len(Child2)>0,'#'&Child2)

            &If(Len(Child3)>0,'#'&Child3)

            &If(Len(Child4)>0,'#'&Child4),'#')+1),'-',1)

Regards!