topic Re: Transpose challenge in QlikView

Transpose challenge

marcel_olmo — Sat, 23 Jan 2016 12:23:54 GMT

Hi guys, I have a big line of inline data which I have to divide it into multiple pieces.

Here's a dummy example of my problem. Imagine I have a table like this :

Customer

James Bond

Spy

Connect

Silvester Stallone

Boxer

Connect

Michael Jackson

Singer

Connect

…

There's a bunch of customers, sepparated with the word "Connect", and I'm trying to divide the Customers, with no exit.

My desired result would be :

ID : 1

Customer Name : James Bond

Customer Profession : Spy

ID : 2

Customer Name : Silvester Stallone

Customer Profession : Boxer

...

Anybody knows how can I do it?

Regards, Marcel.

Re: Transpose challenge

swuehl — Sat, 23 Jan 2016 12:43:06 GMT

Maybe like

INPUT:
LOAD * INLINE [
Customer
James Bond
Spy
Connect
Silvester Stallone
Boxer
Connect
Michael Jackson
Singer
Connect
];


LOAD RowNo() as ID, Customer as Name
Resident INPUT 
WHERE Mod(Recno(),3)=1;


JOIN
LOAD RowNo() as ID, Customer as Profession
Resident INPUT 
WHERE Mod(Recno(),3)=2;

Re: Transpose challenge

marcel_olmo — Sat, 23 Jan 2016 12:47:19 GMT

Thanks swuehl , it's nearly what I want, but the point is that the customer can have 2 rows, 3 rows, or even 4 rows, so the point is to separate it by a key word, in this case the rows containing the word "connect".

Regards, Marcel.

Re: Transpose challenge

swuehl — Sat, 23 Jan 2016 12:53:43 GMT

How do you know which row belongs to which attribute then?

Re: Transpose challenge

marcel_olmo — Sat, 23 Jan 2016 12:58:13 GMT

it's just an example. It's dynamic, that's the difficulty, I have a table with one column where each "customer" can have N properties. The only point is that I know the key row to sepparate it.

Re: Transpose challenge

swuehl — Sat, 23 Jan 2016 13:03:37 GMT

Not sure if I fully understand how you assign your records to customer attributes like profession then, but maybe you can start with something like this:

INPUT:
LOAD Customer, RecNo, Autonumber(Recno(), RecNo) as FieldNo
WHERE Customer <> 'Connect';
LOAD Customer, Rangesum(Alt(Peek(RecNo), 1), -(previous(Customer)='Connect')) as RecNo;
LOAD * INLINE [
Customer
James Bond
Spy
Connect
Silvester Stallone
Boxer
Connect
Michael Jackson
Singer
Connect
];


LOAD RecNo, Customer as Name
Resident INPUT 
WHERE FieldNo=1;


JOIN
LOAD RecNo, Customer as Profession
Resident INPUT 
WHERE FieldNo=2;

Re: Transpose challenge

effinty2112 — Sat, 23 Jan 2016 14:54:21 GMT

Hi Marcel,

Try this:

I've added an extra field for Nationality but omitted a value for for Sly Stallone. This should work for an arbitrary number of fields as long as the the fields are input in the same order for all records. This means that nulls might need to be inserted if a field is empty but the next one in the same record is not.

Data:

Load

If(RecNo()=1,1,

if(Customer = 'Connect',Peek(CustID)+1,Peek(CustID))) as CustID,

If(RecNo()=1,1,

if(Customer = 'Connect',0,Peek(FieldID)+1)) as FieldID,

LOAD * INLINE [

Customer

James Bond

Spy

British

Connect

Silvester Stallone

Boxer

Connect

Michael Jackson

Singer

American

Connect

];

NoConcatenate

NewData:

Load * Resident Data Where FieldID <> 0;

DROP Table Data;

Generic Load

* Resident NewData;

drop Table NewData;

The generic load gives you a data model like this

And this is a simple tablebox

CustID	1	2	3
1	James Bond	Spy	British
2	Silvester Stallone	Boxer
3	Michael Jackson	Singer	American

A mapping table in the load can rename the fields if required.

Hope this helps.

Re: Transpose challenge

petter — Sat, 23 Jan 2016 15:17:45 GMT

Here is my suggestion for two approaches that might work for you with a dynamic number of attributes:

Re: Transpose challenge

MarcoWedel — Mon, 25 Jan 2016 23:21:30 GMT

Hi,

another solution could be:

mapProperties:
Mapping
LOAD AutoNumber(Property), * Inline [
Property
Name
Profession
Favorite Beverage
];

tabTemp:
LOAD CustomID,
    ApplyMap('mapProperties',AutoNumber(If(Customer<>'Connect',RecNo()),CustomID)) as Property,
    Customer;
LOAD *, Alt(Peek(CustomID),1)-(Customer='Connect') as CustomID
Inline [
Customer
James Bond
Spy
"Vodka Martini (shaken, not stirred)"
Connect
Rocky Balboa
Boxer
Egg Shake
Connect
Michael Jackson
Singer
Connect
John Doe
Connect
Jane Doe
unknown
];

tabCustomers:
Generic LOAD * Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

Re: Transpose challenge

rwunderlich — Tue, 26 Jan 2016 01:03:33 GMT

You have a veritable "Forum Who's Who" answering this question! You probably don't need yet another answer, but here's my contribution. (I suppose it could choke on a very large amount of data)

Raw:

LOAD *, RecNo()as RecId INLINE [

Customer

James Bond

Spy

British

Connect

Silvester Stallone

Boxer

Connect

Michael Jackson

Singer

American

Connect

];

Customer:

LOAD

SubField(aCustomer,'|',1 ) as Name

,SubField(aCustomer,'|',2 ) as Profession

,SubField(aCustomer,'|',3 ) as Nationality

WHERE len(aCustomer)>0

;

LOAD

SubField(Alldata, 'Connect|') as aCustomer

;

LOAD

concat(Customer, '|', RecId) & '|' as Alldata

Resident Raw

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com