How to load multiple dynamic fields

[marcel_olmo]

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.

[swuehl · Accepted solution]

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;

[marcel_olmo]

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.

[swuehl]

you can do it like Henric

The Generic Load

[marcel_olmo]

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;