Count 2 fields

[peterderrington]

Hi,

I have two fields in my Qlikview Data - Procedures and 2nd Element

They both contain data that is duplicated between them;

eg Procedures field has: "This", "That", "The other" whilst 2nd Element Field has: "This", "That", "Something else"

I want to create a bar chart that can show the combined totals of the two fields:

eg "This" = 2, "That" = 2, "The other" = 1, "something else" = 1

No matter what expression i put in though i can't get it to work properly.

I'm sure i'm doing something stupid.

What should my Dimension be? I've tried as either one field or the other and both and nothing returns the right result.

Has anyone got any suggestions?

Attached is an example of the Excel Data.

Thanks.

[tresesco]

If you can modify a bit of script like below

you can get it easily in the script like:

[swuehl]

Something like

INPUT:

LOAD Recno() as RecID, *

FROM YourExcelFile;

CROSS:

CROSSTABLE( Field, Value)

LOAD RecID, Procedures, [2nd Element]

RESIDENT INPUT;

Now use Value field as dimension in your chart and

=Count(Value)

as expression.

[peterderrington]

ok.....

The values in the two fields add up to something like 40 distinct values. Do i need to add each value to the script?

[tresesco · Accepted solution]

No, it's just that I have shown using INLINE method. You can get the fields directly from your data source like:

New:

Load

       Procedure as CommonDim

From <>;

Load

       [2nd Element] as CommonDim

From <>;

In that case you would not have to bother about bringing them into the script manually.

[peterderrington]

Thank you so much.

You have no idea how much this has been driving me mad.