topic Re: Calculated Field in QlikView

Calculated Field

Wed, 05 Nov 2014 16:46:56 GMT

Could anyone help me.

I have a dashboard and want to create a new field.

If the field Expense = "Income" then I want my field to have the value in the field "Category"

If the field Expense = "Direct" then I want my field to have the value in the field "Org_Unit"

If the field Expense = "Indirect" then I want my field to have the value in the field "Exp_Catt"

Thanks for your help.

(This is my first Qlikview dashboard, so I will get better !!)

Re: Calculated Field

MK_QSL — Wed, 05 Nov 2014 16:57:10 GMT

You can create below in your script

Load

Value,

Expense,

If(Expense = 'Income', Value) as Category,

If(Expense = 'Direct', Value) as Org_Unit,

If(Expense = 'Indirect', Value) as Exp_Catt,

OtherFields....

From Souc

Re: Calculated Field

alexandros17 — Wed, 05 Nov 2014 16:59:57 GMT

In the script you have something like:

Select (or Load)

...

Expense

...

From

...

Where

...

then transform it into:

Select (or Load)

...

Expense,

If(Expense = 'Income', 'Category',

If(Expense = 'Direct', 'Org_Unit',

If(Expense = 'Indirect', 'Exp_Catt', 'Undefined'

)

) as myNewField

...

From

...

Where

...

Let me know

Re: Calculated Field

Wed, 05 Nov 2014 17:04:02 GMT

Try this in chart's expression:

Pick(Match(GoodField, 'Income', 'Direct', 'Indirect'), 'Category', 'Org_Unit', 'Exp_Catt')

where good field is your field, see attached example.

Re: Calculated Field

its_anandrjs — Wed, 05 Nov 2014 17:04:31 GMT

Provide any sample file.

Regards

Anand

Re: Calculated Field

MarcoWedel — Wed, 05 Nov 2014 17:12:07 GMT

LOAD somefields,

Pick(Match(Expense, 'Income','Direct','Indirect'), 'Category', 'Org_Unit','Exp_Catt') as [new field]

FROM anywhere;

Re: Re: Calculated Field

MarcoWedel — Wed, 05 Nov 2014 21:38:53 GMT

hope this helps

regards

Marco

Re: Calculated Field

MarcoWedel — Wed, 05 Nov 2014 21:41:58 GMT

just noticed my mistake.

Better try:

LOAD somefields,

Pick(Match(Expense, 'Income','Direct','Indirect'), Category, Org_Unit, Exp_Catt) as [new field]

FROM anywhere;

hope this helps

regards

Marco

Re: Re: Calculated Field

MarcoWedel — Wed, 05 Nov 2014 21:46:40 GMT

like this:

LOAD *,
    Pick(Match(Expense, 'Income','Direct','Indirect'), Category, Org_Unit, Exp_Catt) as [new field];
LOAD RecNo() as ID,
    Pick(Ceil(Rand()*3),'Income','Direct','Indirect') as Expense,
    Ceil(Rand()*100) as Category,
    Ceil(Rand()*100) as  Org_Unit,
    Ceil(Rand()*100) as  Exp_Catt
AutoGenerate 20;

hope this helps ...

...finally

regards

Marco