10 Replies Latest reply: May 22, 2014 10:12 AM by Jagan Nalla RSS

    Concatenate data of single field

      Hi All,

       

      I have a question regarding scripting.. I do not know how to implement this one on qlikview..

      I have 2 tables:

       

      table1:

       

      Field1Field2KEYCODE
      abcabcXYZX
      abcabcXYZY
      abcabcXYZZ
      abcabcABCDA
      abcabcABCDB
      abcabcABCDC
      abcabcTSTT
      abcabcTSTS

       

      table2:

      KEYCODE1CODE2CODE3CODE4
      XYZXYZ
      ABCDABCD
      TSTTS

       

      What I want to happen is, I want to create a flag telling me that the data on table1 is complete.

      for example:

      KEY XYZ, codes are: X,Y,Z which is present in table2 (CODE1, CODE2, CODE3)

      now for KEY ABCD, only codes: A,B, and C is present which makes its flag to incomplete.

      As for KEY TST, the codes T and S is both present in table which makes its flag to complete.

       

      How can I achieve this? How can I concatenate the CODE field in table1 to match both tables?

       

      Regards,

      Lyn

        • Re: Concatenate data of single field
          Jagan Nalla

          Hi,

           

          Please check this,

           

          T1:

          LOAD * INLINE [

              Field1, Field2, KEY, CODE

              abc, abc, XYZ, X

              abc, abc, XYZ, Y

              abc, abc, XYZ, Z

              abc, abc, ABCD, A

              abc, abc, ABCD, B

              abc, abc, ABCD, C

              abc, abc, TST, T

              abc, abc, TST, S

          ];

           

           

          T2:

          LOAD * INLINE [

              KEY, CODE1, CODE2, CODE3, CODE4

              XYZ, X, Y, Z,

              ABCD, A, B, C, D

              TST, T, S,

          ];

           

           

          T3:

          CrossTable(Dim,Val)

          LOAD * Resident T2;

           

           

          T4:

          LOAD KEY,Concat(Val) as Con Resident T3 Group By KEY;

          Join

          LOAD KEY,Concat(CODE) as Con1 Resident T1 Group By KEY;

           

           

          LOAD KEY,If(Con=Con1,1,0) as Flag Resident T4;

           

           

          DROP Table T3,T4;

           

          Cheers!!

          Jagan

            • Re: Concatenate data of single field

              Hi Jagan!

               

              thanks for your reply! very nice approach using crosstable thanks! I will try to apply this with real data. Again, thank you

               

              -Lyn

              • Re: Concatenate data of single field

                Hi Jagan!

                 

                I have an additional question. What if my data is like this:

                 

                T1:

                LOAD * INLINE [

                    Field1, Field2, KEY, CODE

                    abc, abc, XYZ, X

                    abc, abc, XYZ, Y

                    abc, abc, XYZ, Z

                    abc, abc, ABCD, A

                    abc, abc, ABCD, B

                    abc, abc, ABCD, C

                    abc, abc, TST, T

                    abc, abc, TST, S

                    abc, abc, TST, S

                    abc, abc, TST, S

                    abc, abc, ABCD, B

                    abc, abc, ABCD, C

                    abc, abc, ABCD, B

                    abc, abc, ABCD, C

                ];

                  • Re: Re: Concatenate data of single field
                    Jagan Nalla

                    Hi,

                     

                    use Distinct in Concat statement.

                     

                    eg:

                    T4:

                    LOAD KEY,Concat(DISTINCT Val) as Con Resident T3 Group By KEY

                    ;

                    Join

                    LOAD KEY,Concat(DISTINCT CODE) as Con1 Resident T1 Group By KEY;

                     

                    Cheers!!

                    Jagan

                      • Re: Re: Concatenate data of single field

                        Hi Jagan,

                         

                        Again thanks! I have another question though, what if my data is like this:

                         

                        T1:

                        LOAD * INLINE [

                            Field1, Field2, KEY, CODE

                            abc, abc, XYZ, X

                            abc, abc, XYZ, Y

                            abc, abc, XYZ, Z

                            abc, abc, ABCD, A

                            abc, abc, ABCD, B

                            abc, abc, ABCD, C

                            abc, abc, TST, T

                            abc, abc, TST, S

                            abc, abc, TST, S

                            abc, abc, TST, S

                            abc, abc, ABCD, B

                            abc, abc, ABCD, C

                            abc, abc, ABCD, O

                            abc, abc, ABCD, N/A



                        What if the CODE is not available in T2?


                        Thanks,

                        Lyn

                  • Re: Concatenate data of single field
                    Prem Kumar Thangallapally

                    hi may be like this  and find attachment

                     

                     

                     

                     

                     

                    T1:

                    Load *,

                    KEY&'-'&CODE as COMPARISIONKEY;

                     

                     

                    LOAD * INLINE [

                        Field1, Field2, KEY, CODE

                        abc, abc, XYZ, X

                        abc, abc, XYZ, Y

                        abc, abc, XYZ, Z

                        abc, abc, ABCD, A

                        abc, abc, ABCD, B

                        abc, abc, ABCD, C

                        abc, abc, TST, T

                        abc, abc, TST, S

                    ];

                     

                    T2:

                    Load *,

                    KEY&'-'&T2.CODE as T2.COMPARISIONKEY,

                    if(Exists(COMPARISIONKEY,KEY&'-'&T2.CODE),0,1) as T2.NUMFLAG,

                    if(Exists(COMPARISIONKEY,KEY&'-'&T2.CODE),'Complete','Incomplete') as T2.COMPLETE_INCOMPLETEFLAG;

                    LOAD *,

                    SubField(CODEFIELD,'!~') as T2.CODE;

                    Load *,

                    KEY as T2.KEY,

                    rowno() as ROWNO,

                    CODE1&'!~'&CODE2&'!~'&CODE3&'!~'&CODE4 as CODEFIELD;

                    LOAD * INLINE [

                        KEY, CODE1, CODE2, CODE3, CODE4

                        XYZ, X, Y, Z,

                        ABCD, A, B, C, D

                        TST, T, S,

                    ];

                     

                     

                    OVERALLSTATUS_MAP:

                    mapping  Load distinct KEY,

                    if(FINALKEY>0,'InComplete','Complete') as T1.TOTAL;

                     

                     

                    Load KEY,

                    sum(T2.NUMFLAG) as FINALKEY

                    Resident T2 where T2.CODE<>'' Group by KEY;

                     

                     

                    INDIVIDUALSTATUS_MAP:

                    Mapping load T2.COMPARISIONKEY,

                    T2.COMPLETE_INCOMPLETEFLAG as T1.INDIVIDUAL

                    Resident T2;

                     

                     

                     

                     

                     

                     

                    FINALT1:

                    Load *,

                    ApplyMap('INDIVIDUALSTATUS_MAP',COMPARISIONKEY,'Others') as T1.INDIVIDUAL,

                    ApplyMap('OVERALLSTATUS_MAP',KEY,null()) as T1.OVERALLSTATUS

                    Resident T1;

                    DROP Table T1;

                     

                     

                     

                     

                    INCOMPLETECODES:

                    LOad KEY,

                    T2.CODE as INCOMPLETECODES

                    Resident T2 where T2.CODE <>'' and  T2.COMPLETE_INCOMPLETEFLAG='Incomplete';

                      • Re: Concatenate data of single field


                        You can also try this:

                         

                        Table:

                        Load * inline [

                        ...

                        ...

                        ];

                         

                        Table_2:

                        noconcatenate

                        load *,

                               len(KEY) as size

                        resident Table;

                         

                        drop table Table;

                            

                        left join(Table_2)

                        load KEY,

                              count(distinct CODE) as attemps

                        resident Table_2;

                         

                        Table_3

                        noconcatenate

                        load *,

                              IF(size = attemps, 'complete',incomplete') as FLAG

                        resident Table_2;

                         

                        drop Table_2

                         

                        regards,

                        Marcelo