Solved: Insert value in pivot table - Qlik Community

Anonymous · ‎2016-12-01

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

rupamjyotidas · ‎2016-12-02

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;

View solution in original post

hemanthaanichet · ‎2016-12-02

Hi Thach

There is a option called suppress zero values try that option

Could provide some sample data?

Regards

Hemanth

Anil_Babu_Samineni · ‎2016-12-02

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2016-12-02

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

Anonymous · ‎2016-12-02

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

pathiqvd · ‎2016-12-02

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

];

rido1421 · ‎2016-12-02

Hi Thach

Green Should be there automatically based on your sample.

could you post a sample qvw?

Anonymous · ‎2016-12-02

Hi,

Can you have a look the attached application

-Jai

Anonymous · ‎2016-12-02

I like your idea. Thanks.

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

Anonymous · ‎2016-12-02

I attach qvd file.

Please give some idea.

Insert value in pivot table

Related Topics