topic Re: Field Issue in QlikView

Field Issue

vanderson009 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I have below data in my table,

Region	Type	Sales
North	Actual	456
East	Actual	348
South	Actual	123
West	Actual	487

I want to display it in pivot table as

But my problem is, I have not Target Type in my Type column, I have only Actual Type, Can you please help me how can i achieve this scenario.

Thanks and Regards,

Villyee.

Re: Field Issue

swuehl — Sat, 19 Mar 2016 14:31:24 GMT

Where do the target values come from?

Do you have a different table with these values?

Or are these random numbers ?

Re: Field Issue

Chanty4u — Sat, 19 Mar 2016 14:32:38 GMT

where that data coming from?

Re: Field Issue

vanderson009 — Sat, 19 Mar 2016 14:35:12 GMT

They are not coming from any table, I have to hard code that values,

that value are range value and i have to display it in Text formate.

On the basis of that range value i have to change background colour of actual value.

Re: Field Issue

vanderson009 — Sat, 19 Mar 2016 14:39:57 GMT

this is actual scenario, On the basis of Region, Range value will be different.

Re: Field Issue

swuehl — Sat, 19 Mar 2016 14:46:26 GMT

Try a second expression labelled 'Target'

=Pick(

Match(Region,'North','East','South','West'),

4000, 4000,3000,3500)

Re: Field Issue

vanderson009 — Sat, 19 Mar 2016 14:58:27 GMT

Thanks swuehl...

I tried but because of second expression one more column is appeared in pivot table but i want row below actual type for every region.

Re: Field Issue

swuehl — Sat, 19 Mar 2016 15:06:16 GMT

Have you tried pivoting your expressions columns to the left, so they appear as expression rows?

Pivot your columns by dragging the headers with the mouse.

Re: Field Issue

vanderson009 — Sat, 19 Mar 2016 15:10:06 GMT

Yes i tried it but by dragging headers into row both expression are coming beside of actual type but i want target should appear below actual.

Attached is result

Re: Field Issue

swuehl — Sat, 19 Mar 2016 15:11:46 GMT

Just rename the first expression from Total Sales to Actual, and remove dimension Type (it's a single value anyway).

Re: Field Issue

swuehl — Sat, 19 Mar 2016 15:15:31 GMT

As an alterantive, create the Target type in the script:

Fact:

LOAD Region, Type, Sales

FROM YourTable;

Concatenate (Fact)

LOAD *, 'Target' as Type INLINE [

Region, Sales

North, 4000

East, 4000

South, 3000

West, 3500

];