Insert value in pivot table

[Anonymous]

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 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

[Anonymous]

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.

[Anonymous]

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

[rupamjyotidas · Accepted solution]

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;

[Anonymous]

Great!

That is what I need, Rupam.

@All: thanks for all your kind reply.