How to dynamically sum to always output the same fields even if the field is not in data?

[KTam0916]

I have data that looks like:

Order	Type	Value
xyz_1	A	2100
xyz_2	B	430
xyz_3	C	51
xyz_4	C	783
xyz_5	B	5441
xyz_6	B	1025

The data may not always have all 3 types of orders A/B/C - on some days, it could be only A/B, or B/C etc. For example:

Order	Type	Value
xyz_1	A	2100
xyz_3	C	51
xyz_4	C	783

Nonetheless, I would like to sum up the total value in such a way that it ALWAYS outputs as, even if one of the types is not present (and as such it should be summed as 0), like so:

A	B	C
2100	0	834

I currently use a sum -> cross tab -> sum, and it works to a degree that it is able to sum up the data correctly but my output would just look like the table below, and it makes it impossible to run the rest of my workflow since a field (B) is missing.

A	C
2100	834

Appreciate any help, thank you! 

[gawa]

hi @KTam0916 The simplest way is to union the dummy data(see below) to the input data, and Cross-tab. 

Type	Value
A	0
B	0
C	0

By doing so, all of type will appear at headers after Cross tab regardless of input data having all of types or not.

I frequently use this technique to maintain the same schema after Cross-tab.(Otherwise missing fields will cause error after cross tab tool)