topic Re: Count If Expression in QlikView

Where Clause

Wed, 25 Nov 2020 16:16:04 GMT

I am new to Qlikview and I am trying to create an expression that counts the number of times a certain [Material] number has the same [Document] number as another certain [Material] number.

Each time a vehicle is sold, the purchase is given a [Document] number. The [Document] number is given to each part [Material] that was sold along with actual vehicle [Material] number. So, a [Document Number] is given to a vehicle [Material] and to each part [Material] when they are sold with.

Here is an example:

Document	Material	Description
12345	618566G01	vehicle
12345	628489	part1
12345	652587	part2
12345	633742	part3

I'm looking for an expression that will count the number of times a part number is associated with a vehicle through a document number.

Re: Count If Expression

vishsaggi — Thu, 29 Jun 2017 21:30:07 GMT

Try This may be?

= Sum(Aggr(Count(Material), Material, Document))

Re: Count If Expression

sunny_talwar — Fri, 30 Jun 2017 02:41:04 GMT

What is the expected output here?

Re: Count If Expression

Fri, 30 Jun 2017 12:58:53 GMT

I need to count the number of times each part number is associated with each vehicle number using the document number.

Re: Count If Expression

vishsaggi — Fri, 30 Jun 2017 15:18:44 GMT

What is your expected output based on the sample data provided in your first thread?

Re: Count If Expression

vishsaggi — Fri, 30 Jun 2017 15:21:08 GMT

Based on your sample is this what you are looking for?

LOAD * INLINE [

Document, Material, Description

12345, 618566G01, vehicle

12345, 628489, part1

12345, 652587, part2

12345, 633742, part3

12345, 633742, part4

12345, 633742, part5

];

Re: Count If Expression

effinty2112 — Fri, 30 Jun 2017 15:36:42 GMT

Hi Brett,

In your script you could split the table into two using the Description field to differentiate between vehicles and parts or use the Material code if there is a logic you can apply there:

Document	Vehicle Material Code	Vehicle Description
12345	618566G01	vehicle

and

Document	Part Material Code	Part Description
12345	628489	part1
12345	652587	part2
12345	633742	part3

Then a selection on Part Material Code will associate with Vehicle Material Code via the Document field.

Cheers

Andrew

Re: Count If Expression

Fri, 30 Jun 2017 17:09:43 GMT

I believe this is what I'm looking for.. I can differentiate vehicles and parts using the string 'G01'. I'm assuming I do this through the use of the subfield function in the script correct?

Re: Count If Expression

effinty2112 — Fri, 30 Jun 2017 17:35:28 GMT

Hi Brett,

Subfield is not the function to use in a where clause. You have a choice. Two suggestions:

WHERE Right(Material,3) = 'G01'

WHERE Wildmatch(Material, '*G01')

Good luck

Andrew