topic Re: Spliting mutliple information from one row into multiple columns in QlikView

Spliting mutliple information from one row into multiple columns

Anonymous — Mon, 11 Jun 2018 14:20:00 GMT

Hello Everywhere,

I have a problem here and hope it is solvable.

I have a table with 2 columns (key_id, content)

An Example would be:

Key ID	Properties
ID_1	shape#circle$color#red$diameter#4inch
ID_2	shape#circle$color#blue$diameter#4inch
ID_3	shape#circle$color#red$diameter#9inch
ID_4	shape#square$color#red$diameter#9inch

Now I filter on color=red and see ID_1, ID_3 and ID_4

Now I would like to continuing to filter. but due to the fact that the property is in one column I'm not able to.

Is it possible to change the row content into columns without knowing the number of properties used and have the column names be set to the property name? A property is separated by $ and the property name is before # and the value after #?

I have 100 different properties and new ones can come in on a random basis so it's hard to track and a lot of maintenance.

Thanks in advance.

Pijatida

Re: Spliting mutliple information from one row into multiple columns

sunny_talwar — Mon, 11 Jun 2018 14:29:35 GMT

Try this

Table:

LOAD [Key ID],

SubField(Properties, '#', 1) as Header,

SubField(Properties, '#', 2) as Value;

LOAD [Key ID],

SubField(Properties, '$') as Properties;

LOAD * INLINE [

Key ID, Properties

ID_1, shape#circle$color#red$diameter#4inch

ID_2, shape#circle$color#blue$diameter#4inch

ID_3, shape#circle$color#red$diameter#9inch

ID_4, shape#square$color#red$diameter#9inch

];

FinalTable:

LOAD DISTINCT [Key ID]

Resident Table;

FOR i = 1 to FieldValueCount('Header')

LET vHeader = FieldValue('Header', $(i));

Left Join (FinalTable)

LOAD DISTINCT [Key ID],

Value as [$(vHeader)]

Resident Table

Where Header = '$(vHeader)';

NEXT

DROP Table Table;

Re: Spliting mutliple information from one row into multiple columns

Anonymous — Mon, 11 Jun 2018 14:33:20 GMT

That would imply that the number of keys is fixed.

I have couple million rows though

Re: Spliting mutliple information from one row into multiple columns

sunny_talwar — Mon, 11 Jun 2018 14:36:15 GMT

Are you talking about the inline load ? That was just for an example... you can use this on top of your existing table like this

Table:

LOAD [Key ID],

SubField(Properties, '#', 1) as Header,

SubField(Properties, '#', 2) as Value;

LOAD [Key ID],

SubField(Properties, '$') as Properties;

LOAD *

FROM .....;

FinalTable:

LOAD DISTINCT [Key ID]

Resident Table;

FOR i = 1 to FieldValueCount('Header')

LET vHeader = FieldValue('Header', $(i));

Left Join (FinalTable)

LOAD DISTINCT [Key ID],

Value as [$(vHeader)]

Resident Table

Where Header = '$(vHeader)';

NEXT

DROP Table Table;