topic Insert value in pivot table in QlikView

Insert value in pivot table

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

Hello,

I used three dimensions:

- Pull: T-shirt, Polo

- Color: Red, Yellow, Blue, Green

-CTC: size 5, size 7....

***The Pull = T-shirt & Color = Green did not have value but Pull = Polo & Color = Green had value as below

I need output template must have T-shirt with Green label as below (I tried all settings: Show All & Suppress, Populate Missing Cells....)

Any suggestion is much appreciate.

Thanks

	V2.Pull	T-shirt	T-shirt	T-shirt	Need "Green" label here		Polo	Polo	Polo	Polo		Grand Total
CTC	Color	Red	Yellow	Blue	Need "Green" label here	Total	Red	Yellow	Blue	Green	Total
size 5		1.0	1.0	1.0	0.0	3.0	1.0	3.0	1.0	1.0	6.0	9.0
size 7		2.0	2.0	2.0	0.0	6.0	0.0	0.0			0.0	6.0
size 8		2.0	5.0	5.0	0.0	12.0	0.0				0.0	12.0
size 9		2.0	2.0	4.0	0.0	8.0	0.0				0.0	8.0
size 98		1.0	1.0	1.0	0.0	3.0					0.0	3.0
size 100		2.0	3.0	3.0	0.0	8.0	2.0	0.0	2.0	2.0	6.0	14.0
Total		10.0	14.0	16.0	0.0	40.0	3.0	3.0	3.0	3.0	12.0	52.0

Header

Re: Insert value in pivot table

hemanthaanichet — Fri, 02 Dec 2016 06:02:40 GMT

Hi Thach

There is a option called suppress zero values try that option

Could provide some sample data?

Regards

Hemanth

Re: Insert value in pivot table

Anil_Babu_Samineni — Fri, 02 Dec 2016 06:14:47 GMT

I am not sure, I understand fully? Would you let us know the output seems How?

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 06:29:52 GMT

Thank Hemanth. I already tried what you suggested but failed.

You can view sample data.

Sorry if my presentation was not clear.

CTC	Color	Size	Qty
T-shirt	Red	Size 5	5
T-shirt	Yellow	Size 5	2
T-shirt	Blue	Size 5	4
T-shirt	Red	Size 7	1
T-shirt	Yellow	Size 7	3
T-shirt	Blue	Size 7	1
T-shirt	Red	Size 8	4
T-shirt	Yellow	Size 8	2
T-shirt	Blue	Size 8	5
T-shirt	Red	Size 9	5
T-shirt	Yellow	Size 9	1
T-shirt	Blue	Size 9	1
T-shirt	Red	Size 95	5
T-shirt	Yellow	Size 95	3
T-shirt	Red	Size 100	3
T-shirt	Yellow	Size 100	3
T-shirt	Blue	Size 100	4
Polo	Red	Size 5	5
Polo	Yellow	Size 5	1
Polo	Blue	Size 5	1
Polo	Green	Size 5	2
Polo	Yellow	Size 7	1
Polo	Blue	Size 7	5
Polo	Yellow	Size 8	3
Polo	Yellow	Size 9	3
Polo	Red	Size 100	5
Polo	Yellow	Size 100	3
Polo	Blue	Size 100	5
Polo	Green	Size 100	5

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 06:38:14 GMT

Thank Anil, let's me clarify, see the below screenshot:

Re: Insert value in pivot table

pathiqvd — Fri, 02 Dec 2016 08:02:01 GMT

Hi,

Try Like This,

Just Concatenate with above table in script level and in properties-->presentation--> disable suppress zero values

Concatenate

LOAD * INLINE [

CTC, Color

T-shirt, Green

];

Re: Insert value in pivot table

rido1421 — Fri, 02 Dec 2016 09:25:17 GMT

Hi Thach

Green Should be there automatically based on your sample.

could you post a sample qvw?

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 09:44:55 GMT

Hi,

Can you have a look the attached application

-Jai

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 10:09:27 GMT

I like your idea. Thanks.

I tried but I did not show as I wish. Please review my sample .qvd

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 10:12:27 GMT

I attach qvd file.

Please give some idea.

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 10:30:57 GMT

Hi Thach,

There are no records for "Green" for T-Shirt. For that reason it will not come in report even if "suppress null" option is disabled.

Re: Insert value in pivot table

Anonymous — Fri, 02 Dec 2016 11:27:44 GMT

Hi ,

Is your requirement is something like Highlighting the "0" values where ever it is possible or only when the Green have the same?

-Jai

Re: Insert value in pivot table

rupamjyotidas — Fri, 02 Dec 2016 14:34:49 GMT

In Script, you can add Color which are not available for CTC.

You can use the below code

InsertValue:

LOAD CTC,

Color,

Size,

Qty,

CTC&Color as Key

FROM

(ooxml, embedded labels, table is [Sample data]);

Temp1:

Load distinct

CTC,

Size

Resident InsertValue;

Join(Temp1)

LOAD * INLINE [

Color

Red,

Yellow,

Blue,

Green

];

Concatenate(InsertValue)

Load * where not Exists(Key);

Load Distinct

CTC,

Size,

Color,

CTC&Color as Key

Resident Temp1;

Drop Table Temp1;

Re: Insert value in pivot table

Anonymous — Sun, 04 Dec 2016 13:46:10 GMT

Great!

That is what I need, Rupam.

@All: thanks for all your kind reply.