topic Excluding records qty but not cost at script level in QlikView

Excluding records qty but not cost at script level

Tue, 21 Dec 2010 20:08:54 GMT

I have a set of claim numbers that are numeric only (ex. 123456) and another set that have letters in them (123456A). I would like to exclude the qty of claims that contain a letter on them but not the cost incurred for these claims. How can I do this at the script level without excluding the entire set of records?

Excluding records qty but not cost at script level

pover — Tue, 21 Dec 2010 20:13:24 GMT

In the Qty field, put a conditional statement

if(isnum(Claim_Number),Qty,0) as Qty

Regards.

Excluding records qty but not cost at script level

Tue, 21 Dec 2010 20:48:44 GMT

Thank you for your response. Here is my scenario.

Claim number Qty Total Cost

000001 1 0.0355

000001A 1 198.35

000001B 1 12.89

000001C 1 35.7

000001D 1 79.54

Need this group to count as 1 claim since the alpha sets are subcategories of same claim.

But need total to include all costs - total $326.52

Excluding records qty but not cost at script level

pover — Tue, 21 Dec 2010 23:03:57 GMT

The script would be something like this then:

Load [Claim number],
firstvalue(Qty) as Qty,
sum([Total Cost]) as [Total Cost]
Resident Table
Group by [Claim number]
Order by [Claim number];

Just test to make sure the main claim number is the first in the table so that firstvalue works or you can try firstsortedvalue() and sort by [Claim number].

Regards.