topic Re: Count 2 fields in QlikView

Count 2 fields

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Count 2 fields

tresB — Thu, 08 Jun 2017 10:25:03 GMT

If you can modify a bit of script like below

you can get it easily in the script like:

Re: Count 2 fields

swuehl — Thu, 08 Jun 2017 10:25:19 GMT

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.

Re: Count 2 fields

peterderrington — Thu, 08 Jun 2017 10:35:41 GMT

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?

Re: Count 2 fields

tresB — Thu, 08 Jun 2017 10:39:33 GMT

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.

Re: Count 2 fields

peterderrington — Thu, 08 Jun 2017 11:13:28 GMT

Thank you so much.

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