topic Re: How to Split single Column value in Mutiple Columns to generate Pivot chart in QlikView

How to Split single Column value in Mutiple Columns to generate Pivot chart

Tue, 06 Nov 2012 10:04:13 GMT

We have a requirment where we need to divide a single Excel coulmn into mutiple columns while fetching data in qvd so that we can generate a pivot chart out of it

The data looks like

Object	Description	Actual Amount	Budget
1NW0001	XYZ	1000	10222
A11101	test	2000	12365
WBS01	test	3000	44354
WBS02	Test	5222	3544
WBS03	test	4646	45354
A11102	test	3544	54345
WBS04	test	1321	34535
WBS05	test	7899	33257

and it should look like

Object	Object1	Object 2
1NW0001
1NW0001	A11101
1NW0001	A11101	WBS01
1NW0001	A11101	WBS02
1NW0001	A11101	WBS03
1NW0001	A11102
1NW0001	A11102	WBS04
1NW0001	A11102	WBS05

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

its_anandrjs — Tue, 06 Nov 2012 10:07:40 GMT

Hi,

Use subfield command for this.

Let me know if not working.

HTH

Regards,

Anand

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

Tue, 06 Nov 2012 10:12:46 GMT

Hi Anand

Thanks for the reply but my requirment is some what different , the values are in a single columns but in different rows and i want to create a hirarchy out of it.

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

Tue, 06 Nov 2012 10:12:59 GMT

Hi Anand

Thanks for the reply but my requirment is some what different , the values are in a single columns but in different rows and i want to create a hirarchy out of it.

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

Tue, 06 Nov 2012 10:13:40 GMT

Hi Anand

Thanks for the reply but my requirment is some what different , the values are in a single columns but in different rows and i want to create a hirarchy out of it.

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

Tue, 06 Nov 2012 10:14:00 GMT

Hi Anand

Thanks for the reply but my requirment is some what different , the values are in a single columns but in different rows and i want to create a hirarchy out of it.

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

its_anandrjs — Tue, 06 Nov 2012 10:14:20 GMT

Hi,

On application loading you have to define the such type of architecture that you want to define for object if the value of the object is fixed or not changeable.

HTH

Regards,

Anand

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

its_anandrjs — Tue, 06 Nov 2012 10:21:47 GMT

Hi,

You have to create a object like

if(Object = '1NW0001',Object) as object,

if(Object = 'A11101',Object) as object1,

if(Object = 'WBS01' or Object = 'WBS02' or Object = 'WBS03',Object) as object2

on the load script.

HTH

Regards,

Anand

Re: How to Split single Column value in Mutiple Columns to generate Pivot chart

Or — Tue, 06 Nov 2012 10:23:33 GMT

You should be able to do something along the lines of (excuse my pseudocode):

Load if(left(Object,1)='W',previous(ParentObject)), if(left(Object,1)<>'1',previous(Object)) as ParentObject, Object

From YourExcel;

Using "Previous" with a conditional should allow you to create an additional two columns which will contain the relevant value or values from the previous row. This assumes your hierarchy is three-tiered (otherwise you'd need more nesting) and that you have a condition for figuring out which hierarchy level each row belongs to. You may have to tweak the syntax some, but the concept should be sound.

Good luck.