Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
marcel_olmo
Partner
Partner

How to load multiple dynamic fields

Hi guys,

I have a table with dynamic structured data like this :

 

IDField1Field2Field3Field4
12 rooms1 bath
2terrace3 rooms2 baths
3garden2 rooms1 bathterrace

And I'd like to have this desired result :

 

IDRoomsBathsTerraceGarden
121
2321
32111

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

4 Replies
swuehl
MVP
MVP

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
Partner
Partner
Author

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
MVP
MVP

you can do it like Henric

The Generic Load

marcel_olmo
Partner
Partner
Author

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
121  
2321 
32111

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;