Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
How do you know which row belongs to which attribute then?
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.
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;
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.
Here is my suggestion for two approaches that might work for you with a dynamic number of attributes:
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
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