Transposing in tablebox

[#1]

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:

A	B
AA12988	BASISSERVICE
AA12988	OLIE
AA12988	VANDUDSKILLER

And I want it to display like this:

A	B	C	D
AA12988	BASISSERVICE	OLIE	VANDUDSKILLER

Is that posible in a tablebox or another box?

[#2]

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.

[#3]

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

[#4]

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 .

[#5]

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

[#6 · Accepted solution]

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 .