Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

skipping the entire row if there is null

Client Service ID

Client Service Name

NSR

No of hours

DTGL503010000

Corporate Finance Advisory

0

591.5

DTGL503040000

M and A Transaction Services

3335613

2139.1

DTGL503050000

Valuation Services

114000

74

if ([SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT]<0 and [SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]> 0) then (null)

else if ([SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT]>0 and [SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]< 0) then (null)

else ([SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]/[SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT])

I have a situation that should calculate the sum(NSR)/sum(no of hours) but It should calculate the sum(NSR) and sum(no of hours)

when the number is neither positive nor negative

If it is either positive or negative or zero then it should skip the entire row and calculate

Ps: if needed any information i can give bit more information but not an sample application

1 Solution

Accepted Solutions
crusader_
Valued Contributor

Re: skipping the entire row if there is null

Hm..

I've changed your scrip like this:

LOAD * INLINE [

   year, A, B, C

   2008, 0, 6, 89

    2009, -6, -7, 6

    2009, -5, -9, 44

    2010, 18, 8, 89

    2011, -, 7, 4

];

And if I reload file 2009 year excluded, because it set in dimension and when you're trying to check if(A*B>0) condition for 1 dimension it find 2 values without aggregation it gives null.

Solution is or to make unique field in dimension, or use aggregation function sum() , avg() etc

in below way:

if( avg()*avg()>0,

     sum({$<A={">0","<0"}>} A)

     /

     sum({$<A={">0","<0"}>} )

     )

Hope this helps.

Andrei

10 Replies
Not applicable

Re: skipping the entire row if there is null

In the above example NSR has a value 0 .so it should skip that row and therefore 591.5 shouldn't be added to the sum(no of hours)

crusader_
Valued Contributor

Re: skipping the entire row if there is null

Hi,

Try this one:

= if( [SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT]*[SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]>0,

     sum({$<NSR-={"0"}>} NSR)

     /

     sum({$<NSR-={"0"}>} [no of hours])

)

Hope this helps you.

Andrei

Not applicable

Re: skipping the entire row if there is null

Adding to Andrei, you can also try the other way(sometimes "0" does not work:

sum({$<NSR={">0"}>} NSR)

     /

     sum({$<NSR={">0"}>} [no of hours])

Thanks,

Singh

Not applicable

Re: skipping the entire row if there is null

Not working in this scenario

Thanks for replying

crusader_
Valued Contributor

Re: skipping the entire row if there is null

Yeah, I agree somtimes "0" doesn't work.

But if we use NSR={">0"} we exclude cases in SUM() function where NSR and TOTAL both negative.

to bhanukiran

What exactly "Not working" means? (null, reduced data or incorrect calculation or smth else)

Please share screenshots.

Thanks

Andrei

Not applicable

Re: Re: skipping the entire row if there is null

Hi Angad,

But when I have two negative values it doesn't work for two positive values it works fine .I am attaching a sample file may be this could help you

But in the sample app my dimension will be not be unique so if I have two years in common then it should add the two years data and followed by that condition

crusader_
Valued Contributor

Re: skipping the entire row if there is null

Use

if( *>0,

     sum({$<A={">0","<0"}>} A)

     /

     sum({$<A={">0","<0"}>} ))

for your example.

It will work.

PFA

Andrei

Not applicable

Re: Re: skipping the entire row if there is null

Crusader_

Not working here represents in the given expression given by you only 1st column value will be verified that the value is negative or positive and it should imply to the next column also so it should check whether the value is positive or negative 

Not applicable

Re: Re: skipping the entire row if there is null

Thanks Andrei thats working but when i have two same years it is not working fine

suppose consider 2009 as 2008 so there will be 2 2008 , then its not working

So, 2008 values should be added and then the expression is to be done

Community Browser