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

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

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

swuehl — Mon, 02 May 2016 17:42:53 GMT

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

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

sunny_talwar — Mon, 02 May 2016 17:46:59 GMT

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

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

Mon, 02 May 2016 18:52:06 GMT

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

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

Mon, 02 May 2016 18:55:10 GMT

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

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

swuehl — Mon, 02 May 2016 19:00:15 GMT

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)