Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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?

1 Solution

Accepted Solutions
Not applicable

Re: Transposing in tablebox

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 .

14 Replies
MVP
MVP

Re: Transposing in tablebox

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))

Not applicable

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

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')))


talk is cheap, supply exceeds demand
Not applicable

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)

Not applicable

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!?

Not applicable

Re: Transposing in tablebox

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 .

Not applicable

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)'

Not applicable

Re: Transposing in tablebox

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 .

Not applicable

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.

Community Browser