topic Re: Straight Table with an unlinked dimension in QlikView

Straight Table with an unlinked dimension

jwaligora — Sat, 16 Nov 2024 21:19:02 GMT

Hi,

I have a data set that looks like this:

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.

Re: Straight Table with an unlinked dimension

Lisa_P — Mon, 04 Mar 2019 03:10:52 GMT

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)

Re: Straight Table with an unlinked dimension

jwaligora — Mon, 04 Mar 2019 22:31:43 GMT

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

Re: Straight Table with an unlinked dimension

Lisa_P — Mon, 04 Mar 2019 23:59:24 GMT

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