topic Check if for dimension value exists already another expression value in QlikView

Check if for dimension value exists already another expression value

davymichiels — Sat, 16 Nov 2024 19:31:24 GMT

Hi there,

I've tried every option I know so I hope somebody can help me out.

First of all : I need to achieve this by set expression, so calculations in the script are not a good solution.

I've got a chart(table) with Date (dim), Order number (dim), Line number (dim), Article number (dim), some other dimensions and some expressions like quantity, value, etc.

This chart is sorted by date.

Important: This chart is already pre-filtered by adding some show_ if-conditions to multiple dimensions. So the count conditions, if used in expressions, must count in the resulting chart and not in the database set of records.

(I)

Now I want to have an expression column that can check for every Article number if it only exists once or not. If not I want also to determine what is the first occurence in the table.

I'm thinking of creating a new field that combines Order number and Order line so I have a unique field (let's say "NR_LINE") and I want to combine that with a count for every article number how many "NR_LINE" there are.

If this count comes op with a result of more than "1" then I need to determine if this is the first occurence or nor (maybe something with below() or above() )

(II)

If we get part (I) right I would also like to take it somewhat further and for every article number that comes up the 2nd time or more, it should mention the Order Number of the previous occurence (2nd time Article number shows 1st time Order number, 3rd shows 2nd, and so on)

Best regards,

Davy

Re: Check if for dimension value exists already another expression value

davymichiels — Tue, 10 Dec 2019 13:46:09 GMT

Nobody has any idea?

Re: Check if for dimension value exists already another expression value

Brett_Bleess — Wed, 11 Dec 2019 21:01:36 GMT

Generally it is most helpful if you attach your source data or QVW you have along with what you want to see, otherwise not betting you are going to get a response from anyone.

Regards,
Brett

Re: Check if for dimension value exists already another expression value

rwunderlich — Wed, 11 Dec 2019 23:11:57 GMT

Can you post a sample QVW? I'm a bit thrown by

"-Important: This chart is already pre-filtered by adding some show_ if-conditions to multiple dimensions. So the count conditions, if used in expressions, must count in the resulting chart and not in the database set of records."

-Rob

Re: Check if for dimension value exists already another expression value

davymichiels — Thu, 12 Dec 2019 07:10:05 GMT

I've attached a sample file to show you what I need to achieve