Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
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

Partner
Partner

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.

MVP
MVP

you can do it like Henric

The Generic Load

Partner
Partner

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;