topic Re: Error in Following script !! in QlikView

Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 06:59:28 GMT

Hello,

Something going wrong in my following script. Please tell me where it is wrong.

Data1:

LOAD Months,

Value,

MissingCount(Item) as CountOfMissingItem

FROM

(ooxml, embedded labels, table is Sheet1) Group By Months ;

------------------------------------------------------------------------------------------------------------------

Months and Item are Characters and Value are Numeric. In Item field i have put some values empty.

Thanks

Re: Error in Following script !!

ashfaq_haseeb — Mon, 17 Nov 2014 07:01:29 GMT

Hi,

Remove group by clause

LOAD Months,

Value,

MissingCount(Item) as CountOfMissingItem

FROM

(ooxml, embedded labels, table is Sheet1) ;

Regards

ASHFAQ

Re: Error in Following script !!

sushil353 — Mon, 17 Nov 2014 07:02:11 GMT

Hi,

the no of field in load/select statement should included in group by clause.. so add Value in your Group by as

Group by Month, Value;

HTH

Sushil

Re: Error in Following script !!

tresB — Mon, 17 Nov 2014 07:03:19 GMT

You have to take all dimensions in the load (except for the one used in aggregation function) under group by clause like:

Group By Months, Value;

Re: Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 07:06:22 GMT

HEy,

Without GroupByClause , MissingCount will not work .

Re: Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 07:09:25 GMT

Hey,

IT works but i could not get what this MissingCount() function returns ??

I thought it will return numeric value(1 , 0) corresponding to other columns where values are missing.

Please explain.

Re: Error in Following script !!

ashfaq_haseeb — Mon, 17 Nov 2014 07:10:17 GMT

If it is function then add other fields to group by clause.

if not then

Try below

LOAD

Months,

only(Value) as Value,

MissingCount(Item) as CountOfMissingItem

FROM

(ooxml, embedded labels, table is Sheet1) Group By Months ;

Regards

ASHFAQ

Re: Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 07:11:43 GMT

Hey,

I just want to know that if we have 'n' no of dimensions then in GroupByClause we have to take 'n-1' dimensions ??

Is it so ??

Re: Error in Following script !!

ashfaq_haseeb — Mon, 17 Nov 2014 07:15:07 GMT

you can use only function as shown above to avoid adding dimension to groupby clause.

Regards

ASHFAQ

Re: Error in Following script !!

tresB — Mon, 17 Nov 2014 07:15:30 GMT

You are right, it returns numeric value. Issue could be somewhere else, like, an understanding-gap between your expectation and our understanding. Hence, try to share a sample data and explain the expected output.

Re: Re: Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 07:18:16 GMT

Hey,

I have attach the application sample. Please check it out and kindly tell me how the output of MissingCount Working here.

Thanks

Re: Error in Following script !!

tresB — Mon, 17 Nov 2014 07:26:27 GMT

This could have been explained the way you want in a class-room training. But here, it would be a bit lenghty, better you narrow down your issue. I would suggest to explain the specific doubt.

Re: Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 07:28:35 GMT

Hey,

According to me ,

Under CountOfMissingItem field, it should show 0 in place where item is missing and 1 in place where item is not missing. But the output is somehow random. Can you explain it now ??

Re: Error in Following script !!

tresB — Mon, 17 Nov 2014 07:39:40 GMT

The source table you have:

And you are counting missing value for Item(and not Value) field and they are missing at position for Months Mar and Apr. It is fine, I see.

Re: Error in Following script !!

nikhilgarg — Mon, 17 Nov 2014 09:02:08 GMT

HEy,

Thanks i got it where i was wrong. I was actually seeing the value instead of item.

Thanks

Re: Error in Following script !!

tresB — Mon, 17 Nov 2014 09:07:38 GMT

Dear Nikhil,

It's a good idea to close the thread by properly marking answer helpfui/rigth answer, it makes other people know that the question/thread is answered and might not require further clarification to it.