Transpose challenge

[marcel_olmo]

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.

[swuehl]

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;

[marcel_olmo]

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.

[swuehl]

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

[marcel_olmo]

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.

[swuehl]

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;

[effinty2112]

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.

[petter]

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

[MarcoWedel]

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

[rwunderlich]

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