Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Not applicable

## Splitting un-orderly, variable number of fields into multiple columns

Hello All,

I have a data manipulation requirement which I am trying to solve using QlikView. I have a table something like this

ID                    Value

1                      1(PC) Material A

2                      1(PC) Material B, 2(PC) Material A

3                      2(PC) Material C, 2(PC) Material B

4                      1(PC) Material A, 1(PC) Material B, 2(PC) Material C, 1(PC) Material D

5

where (PC) is the quantity.

So, as we can see that Value column can have any number of material from 0 to 4, in any order.

Now I have to get a table something like

ID                 Material A         Material B          Material C          Material D          Total

1                       1                                                                                               1

2                       2                         1                                                                    3

3                                                  2                        2                                          4

4                       1                         1                        2                    1                     5

5                                                                                                                        0

Is there a way to do this in QlikView?

thanks,

Manoj

1 Solution

Accepted Solutions
MVP

You can start with something like this to get the quantities and materials per ID:

```
SubField(SubValue,'(PC)',1) as Quantity,
SubField(SubValue,'(PC)',2) as Material;
Subfield(Value,',') as SubValue;
ID,                    Value
1,"1(PC) Material A"
2,"1(PC) Material B, 2(PC) Material A"
3,"2(PC) Material C, 2(PC) Material B"
4,"1(PC) Material A, 1(PC) Material B, 2(PC) Material C, 1(PC) Material D"
];

```

[note: the INLINE table is just to create some sample records]

Then create a pivot table chart with dimensions ID and Material and =Sum(Quantity) as expression:

 ID Material Material A Material B Material C Material D Total 1 1 - - - 1 2 2 1 - - 3 3 - 2 2 - 4 4 1 1 2 1 5
5 Replies
MVP

You can start with something like this to get the quantities and materials per ID:

```
SubField(SubValue,'(PC)',1) as Quantity,
SubField(SubValue,'(PC)',2) as Material;
Subfield(Value,',') as SubValue;
ID,                    Value
1,"1(PC) Material A"
2,"1(PC) Material B, 2(PC) Material A"
3,"2(PC) Material C, 2(PC) Material B"
4,"1(PC) Material A, 1(PC) Material B, 2(PC) Material C, 1(PC) Material D"
];

```

[note: the INLINE table is just to create some sample records]

Then create a pivot table chart with dimensions ID and Material and =Sum(Quantity) as expression:

 ID Material Material A Material B Material C Material D Total 1 1 - - - 1 2 2 1 - - 3 3 - 2 2 - 4 4 1 1 2 1 5
MVP

May be this:

Table:

'Material ' & SubField(Value, 'Material ', 2) as Material,

SubField(Value, '(', 1) as Quantity;

SubField(Value, ',') as Value;

ID|                    Value

1|                      1(PC) Material A

2|                      1(PC) Material B, 2(PC) Material A

3|                      2(PC) Material C, 2(PC) Material B

4|                      1(PC) Material A, 1(PC) Material B, 2(PC) Material C, 1(PC) Material D

] (delimiter is |);

or this:

Table:

Generic

'Material ' & SubField(Value, 'Material ', 2) as Material,

SubField(Value, '(', 1) as Quantity;

SubField(Value, ',') as Value;

ID|                    Value

1|                      1(PC) Material A

2|                      1(PC) Material B, 2(PC) Material A

3|                      2(PC) Material C, 2(PC) Material B

4|                      1(PC) Material A, 1(PC) Material B, 2(PC) Material C, 1(PC) Material D

] (delimiter is |);

Not applicable
Author

Thanks Sunny. However, my 'Material A' does not literally mean Material A but is just a simplified name given to some actual material in the real table.

regards,

Manoj Agrawal

Not applicable
Author

Thanks swuehi,

Your solution seems to work with getting materials and quantities but having issues with pivot table as I have lot of other columns in the table too. Let me see if I need further help on this.

regards,

Manoj

MVP

If you have a fixed number and well known values of Materials, you can also create a bunch of expressions like

=Sum({<Material = {'Material A'}>} Quantity)

=Sum({<Material = {'Material B'}>} Quantity)

=Sum({<Material = {'Material C'}>} Quantity)

=Sum({<Material = {'Material D'}>} Quantity)

=Sum( Quantity)

Community Browser