topic Re: convert row in to column !!! in QlikView

convert row in to column !!!

Tue, 13 May 2014 11:38:58 GMT

Hi All,

I have a Qvd which contains some fields like:

Response _id,

Date,

category ,

Qid,

Response

i want to convert Response row data into column ,i tried with generic load getting ans but data model should not be correct by using generic

this my original data:

Response _id	Qid	Response
1001	100	3
1001	101	5
1001	102	8
1001	103	6

and i want convert into

Response _id	101	101	102	103
1001	3	5	8	6
1002	2	7	8	9

For More clarification see my attachment

can any one help me

please find attachment

Re: convert row in to column !!!

senpradip007 — Tue, 13 May 2014 11:48:33 GMT

PFA

Re: convert row in to column !!!

Peter_Cammaert — Tue, 13 May 2014 11:50:25 GMT

What is wrong with the data model after the GENERIC LOAD?

If you have too many tables, check Henric's post for a trick to

JOIN them together: http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

Best, Peter

Re: convert row in to column !!!

Tue, 13 May 2014 11:50:37 GMT

Hooooo god i tried with the generic load am getting correct ans but when u comes to the data model every field is converted into table

that means

101 field convert in to table

102 field convert in to table

soooo on

i dont want use generic load may i get any solutuion

Re: convert row in to column !!!

Peter_Cammaert — Tue, 13 May 2014 11:53:21 GMT

BTW It's not always "wrong" to get a lot of tables, just like it isn't always "wrong" to have synthetic keys. It just looks ugly in the table viewer...

Re: convert row in to column !!!

Tue, 13 May 2014 11:53:42 GMT

Hi,

Option I can suggest is to load the data in QVW and change the presentation as you would like to see.

PFA

Re: convert row in to column !!!

Tue, 13 May 2014 11:53:55 GMT

can u attach sample Qvw with respective of that ans plss

Re: convert row in to column !!!

anbu1984 — Tue, 13 May 2014 11:54:14 GMT

Try this

Re: Re: convert row in to column !!!

senpradip007 — Tue, 13 May 2014 11:58:29 GMT

PFA

Re: convert row in to column !!!

Tue, 13 May 2014 12:18:17 GMT

thank u but how u can got Horizontal bar in pivot table i did't found in presentation tab also

am getting vertical pivot table so

can u help me pls

Re: convert row in to column !!!

Tue, 13 May 2014 12:25:43 GMT

thank u but how u can got Horizontal bar in pivot table i did't found in presentation tab also

am geting vertical pivot table

can u help me pls

Re: convert row in to column !!!

Peter_Cammaert — Tue, 13 May 2014 12:38:42 GMT

In the Pivot table, grab the dimension you want to make horizontal until a blue arrow appears. Drag the arrow to the top of the Pivot table until it turns sideways. Release the mouse button. Done.

Re: convert row in to column !!!

anbu1984 — Tue, 13 May 2014 12:39:10 GMT

Do you mean horizontal scroll bar? If pivot table is not able to display all dimensions (Here Qid), then it automatically adds horizontal scroll bar

Re: convert row in to column !!!

alexandros17 — Tue, 13 May 2014 12:43:31 GMT

This is the script ypu need, it works, please let me know

AAA:

LOAD * Inline [

Response_id, Qid, Response

1001 ,100, 3

1001 ,101, 5

1001 ,102, 8

1001 ,103, 6];

FOR i = 1 to NoOfRows('AAA')

LET id = Peek('Response_id', $(i)-1, 'AAA');

LET qu = Peek('Qid', $(i)-1, 'AAA');

Tmp_$(i):

LOAD Response_id, Response as $(qu) Resident AAA Where Response_id = $(id) and Qid = $(qu);

BBB:

NoConcatenate

LOAD Distinct Response_id Resident AAA;

FOR i = 1 to NoOfRows('AAA')

Left Join (BBB)

LOAD * Resident Tmp_$(i);

DROP Table Tmp_$(i);

DROP Table AAA;

Re: convert row in to column !!!

Tue, 13 May 2014 12:57:09 GMT

No no not horizontal scroll bar

see my attachment

when u r doing this that pivot table will come in horizontal

when am doing same thing it will come in vertical

Re: convert row in to column !!!

anbu1984 — Tue, 13 May 2014 13:04:59 GMT

Is this you were referring? You have to click on Qid and then drag and drop horizontally

Re: convert row in to column !!!

anbu1984 — Tue, 13 May 2014 13:10:29 GMT

Check this video at 5:50

https://www.youtube.com/watch?v=x0-38NZiXS0

Re: convert row in to column !!!

Anonymous — Fri, 09 Jan 2015 12:01:09 GMT

Hi Pradip,

Your example it is what I need.

Please where exactly do I have to change to get exactly this display? I saw in you example and the tab "Presentation" looks the same for all.

Could you help me?

Thank you!