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

[#1]

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

[swuehl · Accepted solution]

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

LOAD *,
  SubField(SubValue,'(PC)',1) as Quantity,
  SubField(SubValue,'(PC)',2) as Material;
LOAD *,
  Subfield(Value,',') as SubValue;
LOAD * INLINE [
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

[sunny_talwar]

May be this:

Table:

LOAD ID,

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

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

LOAD ID,

  SubField(Value, ',') as Value;

LOAD * Inline [

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

LOAD ID,

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

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

LOAD ID,

  SubField(Value, ',') as Value;

LOAD * Inline [

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 |);

[#4]

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

[#5]

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

[swuehl]

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)