Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a table with dynamic structured data like this :
ID | Field1 | Field2 | Field3 | Field4 |
1 | 2 rooms | 1 bath | ||
2 | terrace | 3 rooms | 2 baths | |
3 | garden | 2 rooms | 1 bath | terrace |
And I'd like to have this desired result :
ID | Rooms | Baths | Terrace | Garden |
1 | 2 | 1 | ||
2 | 3 | 2 | 1 | |
3 | 2 | 1 | 1 | 1 |
How could I get that if the field1,field2, fieldN could be filled with any possible value which later will be my desired dimension?
Regards, Marcel.
Maybe using a crosstable load to get all informations into a single field, then separate numbers from text and map text type. Finally use a Generic load to create fields from type (if needed at all, you can do most analysis using the count & type field structure).
INPUT:
LOAD * INLINE [
ID Field1 Field2 Field3 Field4
1 2 rooms 1 bath
2 terrace 3 rooms 2 baths
3 garden 2 rooms 1 bath terrace
] (delimiter is '\t');
TMP:
CrossTable (Field, Value)
LOAD * Resident INPUT;
Generic
LOAD ID, Type, Count
WHERE Len(Trim(Type));
LOAD ID,
Value,
If(Len(Trim(Keepchar(Value,'0123456789'))),Keepchar(Value,'0123456789'),1) as Count,
Pick(WildMatch(lower(Value),'*garden*','*terrace*','*room*','*bath*'),'Garden','Terrace','Room','Bath') as Type
Resident TMP;
DROP TABLE TMP;
Maybe using a crosstable load to get all informations into a single field, then separate numbers from text and map text type. Finally use a Generic load to create fields from type (if needed at all, you can do most analysis using the count & type field structure).
INPUT:
LOAD * INLINE [
ID Field1 Field2 Field3 Field4
1 2 rooms 1 bath
2 terrace 3 rooms 2 baths
3 garden 2 rooms 1 bath terrace
] (delimiter is '\t');
TMP:
CrossTable (Field, Value)
LOAD * Resident INPUT;
Generic
LOAD ID, Type, Count
WHERE Len(Trim(Type));
LOAD ID,
Value,
If(Len(Trim(Keepchar(Value,'0123456789'))),Keepchar(Value,'0123456789'),1) as Count,
Pick(WildMatch(lower(Value),'*garden*','*terrace*','*room*','*bath*'),'Garden','Terrace','Room','Bath') as Type
Resident TMP;
DROP TABLE TMP;
Thanks Stefan! It's just what I want. The only point I miss here is how can I join the desired final table from the generic load point.
For example ordering by namefield.
Regards, Marcel.
you can do it like Henric
Thanks Stefan! I've just took the version of Rob, because It adds a wildmatch check for the name of the table to match.
Here's my final result fine :
ID | Room | Bath | Terrace | Garden |
---|---|---|---|---|
1 | 2 | 1 | ||
2 | 3 | 2 | 1 | |
3 | 2 | 1 | 1 | 1 |
And the code to make it work :
GenericLabel :
Generic
LOAD ID, Type, Count
WHERE Len(Trim(Type));
LOAD ID,
Value,
If(Len(Trim(Keepchar(Value,'0123456789'))),Keepchar(Value,'0123456789'),1) as Count,
Pick(WildMatch(lower(Value),'*garden*','*terrace*','*room*','*bath*'),'Garden','Terrace','Room','Bath') as Type
Resident TMP;
CombinedGenericTable :
LOAD ID Resident TMP;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'GenericLabel.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
DROP TABLE TMP;