topic Re: Transpose challenge in QlikView

Transpose challenge

marcel_olmo — Mon, 08 Dec 2014 22:16:55 GMT

Hi guys, I'm trying to do a particular transpose with no exit.

Here I attach an image of my previous result, what I'm trying right now, and my desired result :

As you can see, I have two fields, the first one has the headers of the columns (the dimensions), and the second one has their values.

Do you know how can I get my desired result? I bet it's easier than I think but I'm really stuck with that stuff.

If you want to try it, here I attach the document.

Regards, Marcel.

Re: Transpose challenge

afurtado — Mon, 08 Dec 2014 23:46:32 GMT

Hi.

I hope this can help you.

Best,

Alessandro Furtado

Re: Transpose challenge

MarcoWedel — Tue, 09 Dec 2014 00:13:36 GMT

Hi,

one solution could be:

Generic LOAD
    RecNo() as ID,
    SubField([Table Header], ', ', IterNo()) as Header,
    SubField([Table Line], ', ', IterNo()) as Line
INLINE [
    Table Header, Table Line
    "Field1, Field2, Field3", "1, 2, 3"
]
While IterNo()<=RangeMax(SubStringCount([Table Header],', '),SubStringCount([Table Line],', '))+1;

hope this helps

regards

Marco

Re: Transpose challenge

marcel_olmo — Tue, 09 Dec 2014 00:29:31 GMT

Thanks Marco! It's almost what I need, could you do it using my example.qvd instead of the inline sentence? Imagine that there are more rows than 1.

I mean :

Field1,Field2,Field3 1,2,3

Field1,Field2,Field3 4,5,6

...

Field1,Field2,Field3 X,X,X

Regards, Marcel.

Re: Re: Transpose challenge

MarcoWedel — Tue, 09 Dec 2014 00:55:20 GMT

like this?

Previous:
Generic LOAD 
    RecNo() as ID, 
    SubField([Table Header], ', ', IterNo()) as Header, 
    SubField([Table Line], ', ', IterNo()) as Line
FROM Example.qvd (qvd)
While IterNo()<=RangeMax(SubStringCount([Table Header],', '),SubStringCount([Table Line],', '))+1;

hope this helps also

regards

Marco

Re: Re: Re: Transpose challenge

marcel_olmo — Tue, 09 Dec 2014 01:24:13 GMT

Thanks Marco, Here I attack my final result joining the general load table :

Regards, Marcel.