Straight Table with an unlinked dimension

[jwaligora]

Hi,

I have a data set that looks like this:

ID | Exterior_Colour | Interior_Colour
1 | Blue | Grey
2 | Blue | Yellow
3 | Yellow | Yellow
4 | Green | Grey

Etc.

I need to generate a table that looks like this (where the dimension belongs to an independently defined list of "special colours":

Colour | Count of Exterior | Count of Interior
Green | 1 | 0
Grey |  0 | 2
Red | 0 | 0
Yellow |  1 | 2

I thought I could load the list of "special colours" as a separate (unlinked) table, and use it as a dimension in a straight table, but I can't seem to conjure up an expression that recognizes the row value it should correspond to. In Excel this would be the most straightforward thing...Sum(if(Range=Colour_Cell, 1,0)) [Shift+Enter], but in QV this only only seems to work on linked tables.

Is there a way? Will I need to modify the data at the script level?

Thanks,
Joanna.

[Lisa_P · Accepted solution]

Simple to do at Script level.
Colours:
LOAD ID,
Exterior_Colour,
Interior_Colour
FROM
[C:\Users\Lisa\Documents\Community\colourdata.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Colours2:
Load ID,
[Exterior_Colour] as Colour,
'Ext' as ColourType
Resident Colours;

Load ID,
[Interior_Colour] as Colour,
'Int' as ColourType
Resident Colours;
then just create a straight table with Colour as dimension and have two columns to count(Colour) then add set analysis per column to define Interior or Exterior - Count({<ColourType={Ext}>}Colour) and Count({<ColourType={Int}>}Colour)

[jwaligora]

Thanks. That definitely works. Is it conclusive then, that there is no way to achieve this end via post-load means?

[Lisa_P]

There likely is, but it becomes more complex and will take longer to calculate on the front end, meaning worse performance