14 Replies Latest reply: Apr 11, 2013 5:28 AM by Yusuf Ali RSS

    Transposing in tablebox

      In this TABLEBOX I want to "transpose" data, so identical values in column A will appear only once and the different values in column B are shown in the same row in seperate columns.

       

      I looks like this now:

       

       

      AB
      AA12988BASISSERVICE
      AA12988OLIE
      AA12988VANDUDSKILLER

       

       

      And I want it to display like this:

       

      AB CD
      AA12988BASISSERVICEOLIEVANDUDSKILLER

       

       

      Is that posible in a tablebox or another box?

        • Re: Transposing in tablebox
          Stefan Wühl

          I don't think this can be done in a table box.

           

          If your max number of B values per unique A value is somewhat fixed and small, you can try something like described here:

          http://community.qlik.com/message/311321#311321

           

          (you can also achieve something similar by remodelling your data using a generic load (introducing another field labelling your B value numbers))

          • Re: Transposing in tablebox

            Sorry, I just have a private free License at the moment, so I can't open the Qkliview-document, can you pose the script here in the text. Or maybee save it as a Script-file, "QVS"?

             

            Message was edited by: hvid3600

              • Re: Transposing in tablebox
                Gysbert Wassenaar

                Well, one example hardcodes the fields. If you have more than four B values per A then you need to manually add more subfield(... lines.

                 

                T2:

                load

                          A,

                          subfield( concat(B, '|', FieldIndex('B',B)),'|',1) as B,

                          subfield( concat(B, '|', FieldIndex('B',B)),'|',2) as C,

                          subfield( concat(B, '|', FieldIndex('B',B)),'|',3) as D,

                          subfield( concat(B, '|', FieldIndex('B',B)),'|',4) as E

                group by A;

                LOAD * INLINE [

                    A, B

                    AA12988, BASISSERVICE

                    AA12988, OLIE

                    AA12988, VANDUDSKILLER

                    BA12981, KOFFIE

                    BA12981, THEE

                    BA12981, LIMONADE

                    BA12981, VIEZE BOEKJES

                ];

                 

                The other solution uses a pivot table. In that case I didn't use the preceding load with the subfield expressions, only the inline load. The pivot table has two dimensions and one expression. As column dimension A and as row dimension a calculated dimension =ValueLoop(1,FieldValueCount('B')). The expression used is SubField( Concat(B, '|', FieldIndex('B',B)),'|', ValueLoop(1,FieldValueCount('B')))

                  • Re: Transposing in tablebox

                    Thanks, now I'm getting close, but I have a challenge with the inline load - I don't know all the "A" values in advance and they would be different from time to time, but in fact all "B" values are defined (only abot 7 different values). The "A" values are car registration numbers, so they have the same format every Time - 2 letters and 5 numbers. Can we use that information to write a code that will recognize that? (Just for additional information: All the B values are Service Points that the cars need to have performed)

                      • Re: Transposing in tablebox

                        Another thought - if you can get a pivotabel to display text in the fields instead of numbers, then it should also be a solution, but I can’t make it do so. It will always show a sum or number, but actually I just had to have the pivotabel to write the title of the column in the row field, if there was a value - and the value in this case will always be one, so if you can code the pivotabel to write the column header in the row field, if the value is one, then I would imagine it could be a solution!?

                  • Re: Transposing in tablebox
                    Yusuf Ali

                    HI ,

                          Try the Below code .

                           It will give the Desired Output with the Fields as A ,B ,C ,D .......


                          You can use TableBox to Display the Fields .

                     

                    Exa:

                    LOAD * Inline

                    [

                      A,         B

                      AA12988,   BASISSERVICE  

                      AA12988,   OLIE

                      AA12988,   VANDUDSKILLER

                    ];

                     

                     

                    Dummy:

                    LOAD * Inline

                    [

                    Alphabet

                    B

                    C

                    D

                    E

                    F

                    G

                    H

                    ];

                     

                     

                    Columnlist:

                        LOAD

                           Distinct B as AN

                           Resident Exa;

                     

                     

                    Let v_first_attr = Peek('AN',0,'Columnlist');

                    Let v_attr_list = '';

                    Let j= NoOfRows('Columnlist')-1;

                      For i=0 to j

                       Let v_attr_list = v_attr_list & chr(39)& Peek('AN',i,'Columnlist') & chr(39) & if(i<>j,',');

                      NEXT

                     

                     

                     

                     

                    let k=0;

                    for Each attr in $(v_attr_list)

                    Let AlField=Peek('Alphabet',k,'Dummy');

                    if '$(attr)' = '$(v_first_attr)'  then

                     

                     

                    NoConcatenate

                               NewData:

                                         LOAD  B as '$(AlField)'  , A

                                         Resident Exa

                                         Where B= '$(attr)';

                     

                               ELSE

                     

                               Join(NewData)

                                         LOAD  B as '$(AlField)' , A

                                         Resident Exa

                                         Where B= '$(attr)';

                     

                     

                    ENDIF

                    let k= $(k)+1;

                     

                    NEXT

                     

                    DROP Table Columnlist ,Dummy,Exa;

                     

                     

                    Hope It Helps you .

                      • Re: Transposing in tablebox

                        Thanks

                         

                        I unfortunately get this Error, when loading the script, and can't find out what's the problem:

                         

                         

                        Blank field name not allowed
                        NoConcatenate
                        NewData:
                        LOAD B as '' , A
                        Resident Exa
                        Where B= ''

                         

                        Can you?

                         

                        I can see the scrpit thinks there is a blank field name, but when I look in your script it doesn't look like there is a problem. It seems like the scrpit won't read  '$(A1field)'

                         

                         

                          • Re: Transposing in tablebox
                            Yusuf Ali

                            THe HI ,

                                   For me its worknig fine , Are you missing anything from the above code ?

                            You just copy the entire code and try to run .

                             

                            The Error might be due to i think you didn't copied the Dummy Table.

                             

                            Dummy:

                            LOAD * Inline

                            [

                            Alphabet

                            B

                            C

                            D

                            E

                            F

                            G

                            H

                            ];

                             

                             

                             

                             

                            The Output will be shown as below .

                             

                             

                            A B C D
                            AA12988 BASISSERVICE OLIE VANDUDSKILLER

                             

                             

                            Please let me know if it continues .

                              • Re: Transposing in tablebox

                                Yes I tried to "Comment" all my own script, and then your script is working

                                 

                                So it must be the connection between your script and my script that causes the error.

                                 

                                I must replace your

                                "Exa:

                                LOAD * Inline"

                                 

                                with the real script which is loading the data - am I correct?

                                 

                                My script is attached in the file - and "Reg.nr. is what we have called Column A and "Servicebeskrivelse" colomn B.

                                  • Re: Transposing in tablebox
                                    Yusuf Ali

                                    HI ,

                                        please find the code below .

                                    DIRECTORY;

                                    Temp:

                                    LOAD @1 as Reg.nr.,

                                         @2 as ID1,

                                         @3 as ID2,

                                         @4 as Op.nr.,

                                         @5 as Indkaldelsdato,

                                         @6 as Servicebeskrivelse,

                                         @7 as ID3

                                    FROM

                                    THFQFTPSED.txt

                                    (txt, codepage is 1252, no labels, delimiter is ';', msq);

                                     

                                     

                                    Exa:

                                     

                                    LOAD

                                    Reg.nr., Servicebeskrivelse

                                    Resident Temp;

                                     

                                     

                                     

                                     

                                    Dummy:

                                     

                                    LOAD * Inline

                                     

                                    [

                                     

                                    Alphabet

                                     

                                    B

                                     

                                    C

                                     

                                    D

                                     

                                    E

                                     

                                    F

                                     

                                    G

                                     

                                    H

                                     

                                    ];

                                     

                                     

                                     

                                     

                                     

                                    Columnlist:

                                     

                                    LOAD

                                    Distinct Servicebeskrivelse as AN

                                    Resident Exa;

                                     

                                     

                                     

                                     

                                     

                                    Let v_first_attr = Peek('AN',0,'Columnlist');

                                     

                                    Let v_attr_list = '';

                                     

                                    Let j= NoOfRows('Columnlist')-1;

                                     

                                      For i=0 to j

                                     

                                       Let v_attr_list = v_attr_list & chr(39)& Peek('AN',i,'Columnlist') & chr(39) & if(i<>j,',');

                                     

                                      NEXT

                                     

                                     

                                     

                                    let k=0;

                                     

                                    for Each attr in $(v_attr_list)

                                     

                                    Let AlField=Peek('Alphabet',k,'Dummy');

                                     

                                    if '$(attr)' = '$(v_first_attr)'  then

                                     

                                     

                                     

                                    NoConcatenate

                                                NewData:

                                                        LOAD  Servicebeskrivelse as '$(AlField)'  , Reg.nr

                                                        Resident Exa

                                                        Where Servicebeskrivelse = '$(attr)';

                                     

                                                ELSE

                                     

                                                Join(NewData)

                                                        LOAD  Servicebeskrivelse as '$(AlField)' , Reg.nr

                                                        Resident Exa

                                                        Where Servicebeskrivelse = '$(attr)';

                                     

                                     

                                    ENDIF

                                     

                                    let k= $(k)+1;

                                     

                                     

                                    NEXT

                                     

                                     

                                     

                                    DROP Table Columnlist ,Dummy,Exa;

                                     

                                     

                                    If there is anything please let me know.

                                      • Re: Transposing in tablebox

                                        Thought that i would work now, but I get this error:

                                        Field not found - <Reg.nr>
                                        NoConcatenate

                                                    NewData:

                                                            LOAD  Servicebeskrivelse as 'B'  , Reg.nr

                                                            Resident Exa

                                                            Where Servicebeskrivelse = '2009-10-30'

                                         

                                         

                                        But we also need to integrate this part of the scrpit (former attched in word doc.)

                                         

                                        I have attached the rest of the script and my files, maybe it is possible for you to se what goes wrong?

                                         

                                        BR Kenneth

                                          • Re: Transposing in tablebox
                                            Yusuf Ali

                                            Hi ,

                                                  The Exact field Name is "Reg.nr."   earlier in my post by mistake i wrote as "Reg.nr" ( without Dot at the end of the field name ).

                                             

                                            So here use as

                                            NoConcatenate

                                                        NewData:

                                                                LOAD  Servicebeskrivelse as 'B'  , Reg.nr. 

                                                                Resident Exa

                                                                Where Servicebeskrivelse = '2009-10-30'

                                             

                                             

                                            Please let me know if there is anything .

                                              • Re: Transposing in tablebox

                                                Hi

                                                 

                                                Ah, yes a single dot can be crucial!

                                                 

                                                But, why did you change " Where Servicebeskrivelse = '$(attr)'; " to " Where Servicebeskrivelse = '2009-10-30' " ?

                                                 

                                                I can't see that it will transpose the rows to columns?

                                                 

                                                If correct the mistake and put in the dot and load with "Where Servicebeskrivelse = '$(attr)' " I still get this error as displayed below. If I reply "OK" to the Error (instead of cancel) the error seems to come for maybe every single line in the data, because I can see that the text changes every time - as seen in the example below (Where Servicebeskrivelse = 'FULDSERVICE EXCL. SMØRING OG OLIESKIFT'  -  the part after = changes and what is displayed is the changing text from the data load, these different text's that should bed transposed to columns insted of rows)

                                                 

                                                Blank field name not allowed
                                                Join(NewData)

                                                                    LOAD  Servicebeskrivelse as '' , Reg.nr.

                                                                    Resident Exa

                                                                    Where Servicebeskrivelse = 'FULDSERVICE EXCL. SMØRING OG OLIESKIFT'

                                                 

                                                Hope my explanation makes sense

                                                 

                                                BR Kenneth

                                                  • Re: Transposing in tablebox
                                                    Yusuf Ali

                                                    Hi Kenneth ,

                                                                         At present i see the Number of Unique values in the field "Servicebeskrivelse" is around 67 .

                                                    So for that you need to have the Fields Names difined for 67 times .

                                                     

                                                    Here i defined just for 6 (B , C,D , E F , G , H)  so like that you have to

                                                    defined for more means ( I,J,K,L,M,N.......so on till 67 names ).

                                                     

                                                    So you have to generate the 67 names in the below table :

                                                     

                                                    Dummy:

                                                     

                                                    LOAD * Inline

                                                     

                                                    [

                                                    Alphabet

                                                     

                                                    B

                                                    C

                                                    D

                                                    E

                                                    F

                                                    G

                                                    H

                                                    .

                                                    .

                                                    .

                                                    .

                                                     

                                                    ];

                                                     

                                                    Please let  me know for further .