Where Clause

[#1]

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.

[vishsaggi]

Try This may be?

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

[sunny_talwar]

What is the expected output here?

[#4]

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

[vishsaggi]

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

[vishsaggi]

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

];

[effinty2112 · Accepted solution]

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

[#8]

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?

[effinty2112]

Hi Brett,

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

WHERE Right(Material,3) = 'G01'

or

WHERE Wildmatch(Material, '*G01')

Good luck

Andrew