topic Re: help with an expression in QlikView

help with an expression

Anonymous — Mon, 31 Mar 2014 08:26:29 GMT

I think I'm overlooking something ... maybe someone can help me get on the track again.

I have a table which looks something like this:

Servicefact.ServiceOrderNo

Servicefact.RowNo

Servicefact.ArticleCode

Servicefact.Article

....

A ServiceOrderNo only has 1 Article (so no different articles)

A ServiceOrderNo zero or more ArticleCode (Parts that are used)

e.g.

ServiceOrderNo	RowNo	ArticleCode	Article
SO1234	1	CR3032	PP01
SO1234	2	IP20102/R	PP01
SO1235	1	CR3032	PP02
SP1236	1	AT212/R2	PP01
SP1237	1	CR3023	PP02
SP1237	2	AT2112/R2	PP02
SP1238	1	CR3023	PP03

I have overviews for:

Article - ArticleCode ... number of ServiceOrderNo.

But now I'm looking for the opposite, but then a little different.

ArticleCode - total number of ServiceOrderNo for Article (on which the ArticleCode is used)

also the ServiceOrderNo which have the same Article but not the ArticleCode!!!

e.g.

ArticleCode	NumberOfSO	*explanation*
CR3032	5	is used in articles PP01/PP02/PP03 -- sum of all article (also where ArticleCode is not used)
IP20102/R	2	is used in article PP01 -- sum PP01 serviceorderno
AT212/R2	4	is used in article PP01/PP02 -- sum of PP01 / PP02 serviceorderno
...	...

Anyone got a good suggestion how to fix this one??

thx in advance!!

Anita

Re: help with an expression

Michiel_QV_Fan — Mon, 31 Mar 2014 08:38:01 GMT

Untested:

if(isnull(ArticleCode) = 0 and isnull(ArticleCode) = 0, count(ServiceOrderNo), 0) for counting where article where ArticleCode is used.

if(isnull(ArticleCode) = 0, count({1<Article = p(Article)>}ServiceOrderNo)) for the second one

Re: help with an expression

Anonymous — Mon, 31 Mar 2014 08:54:31 GMT

Never used the part:

{1<Article = p(Article)>}

Using this it seems my date selection is not taken in to account (didn't mentioned this in the example, but there is also a date involved).

rgrds Anita

Re: help with an expression

Mon, 31 Mar 2014 09:03:19 GMT

What about 1 to $ to use other filters?

{$<Article = p(Article)>}

Re: help with an expression

Mon, 31 Mar 2014 09:11:52 GMT

Why not just use an open declaration for the ArticleCode and then the possible for article.

So it would be Count({<ArticleCode = , Article = P(Article)>}ServiceOrderNo).

Also are you trying to use this for a table or just as a one off expression, since the dimensions of the table may mean you need different expression.

Re: help with an expression

Anonymous — Mon, 31 Mar 2014 09:40:44 GMT

I have a date selection (e.g. year/month) ...

And the only dimension used is ArticleCode ... but the expression needs the Article count ...

I already tried to use Count({<ArticleCode = , Article = P(Article)>}ServiceOrderNo) but this didn't do the trick.

rgrds

Re: help with an expression

Mon, 31 Mar 2014 10:11:12 GMT

If you want to pull the value into a table then it's much easier to just calculate it in the back end.

Just use the group function in your script to get a number, mine looked like this:

NumberOfSO:

Load

Article,

Count(ServiceOrderNo) as NumberOfSO

Resident Data

Group By Article;

Obviously you would need to use whatever resident you are holding the data in. To get the value you are after then just use sum(NumberOfSO).