topic Re: Qlik Sense: Compare dimension values to another dimension in App Development

Qlik Sense: Compare dimension values to another dimension

Oguzhan — Sat, 16 Nov 2024 18:04:38 GMT

Hello,

I have following situation: I have a table like below, and I need to compare the status value with a order number and if all order numbers have the status "done" then I need QlikSense to display 'done' otherwise the status should say 'open'

Tasknumber	Status	Order number
1	done	0111
2	done	0111
3	done	0111
4	done	0112
5	done	0112
6	open	0112
7	open	0112

I want to show the Order number in a table like this. Order number 0111 is done because all tasks underneath the Order are done. Order number 0112 is open because 2 tasks are done but there are still 2 tasks open so the overall status is open.

Order number	Status Order
0111	done
0112	open

So far I have tried it with.

Aggr(If(Status='done', 'done', 'open'), Order number,Status)

but that didn't work as expected.

Thank in advance for any help.

Re: Qlik Sense: Compare dimension values to another dimension

Taoufiq_Zarra — Wed, 23 Sep 2020 13:27:07 GMT

@Oguzhan

you can try for example :

in dimension : Order Number

In expression :

if(count({<[Order number]={"=count({<Status={'open'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)

output:

Re: Qlik Sense: Compare dimension values to another dimension

asinha1991 — Wed, 23 Sep 2020 13:53:36 GMT

What Taoufiq suggested should work, for some reason if you want it to be a dimension and not measure, you can do this

if(aggr(count({<Status={'open'}>} Tasknumber),[Order number])>=1,'open',Status)

Re: Qlik Sense: Compare dimension values to another dimension

Oguzhan — Thu, 24 Sep 2020 07:23:27 GMT

@Taoufiq_Zarra Thanks for your help. Your code seems to work, if there are only 2 type of status.

How do I handle this code if there are more than 2 statuses like 'open' , 'done' , 'confirmed'

I also added the dimension priority, and for me only K1 Priority is neccessary

Priority	Tasknumber	Status	Order number
K0	1	done	0111
K1	2	done	0111
K1	3	done	0111
K0	4	done	0112
K1	5	done	0112
K1	6	open	0112
K1	7	open	0112
K1	8	confirmed	0112

I modified your code like this, but it doesn't give the correct status for the whole dataset.

if(count({<Priority={'K1'},[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)

Re: Qlik Sense: Compare dimension values to another dimension

Taoufiq_Zarra — Thu, 24 Sep 2020 08:22:45 GMT

@Oguzhan

can you share the expected output from this sample ?

Re: Qlik Sense: Compare dimension values to another dimension

Oguzhan — Thu, 24 Sep 2020 08:34:07 GMT

@Taoufiq_Zarra .

I expect something similar like this.

Priority	Order number	Status Order
K0	0111	done
K0	0112	open

Order number 0111 is closed because all 'K1' task are closed.

Order number 0112 is open because there are still 'open' and 'confirmed' tasks

The logic in my table is like :

Priority 'K0' is my so called head order and the 'K1' tasks are "subtasks".

An order of the Priority K0 is fullfilled and done if all the 'K1' task lying underneath the head order are closed.

During the process the "subtasks" can have the statuses 'open' or 'confirmed'

Re: Qlik Sense: Compare dimension values to another dimension

Taoufiq_Zarra — Thu, 24 Sep 2020 08:51:30 GMT

@Oguzhan in this case you need to ignore Priority in measure like

in dimension

=if(Priority='K0',Priority) and chek Supress when value is null

and dimension Order Number

and for measure :

if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',Status)

output like :

Re: Qlik Sense: Compare dimension values to another dimension

Taoufiq_Zarra — Thu, 24 Sep 2020 08:57:04 GMT

@Oguzhan output

Re: Qlik Sense: Compare dimension values to another dimension

Oguzhan — Tue, 27 Oct 2020 09:34:36 GMT

@Taoufiq_Zarra Why can't I write an alternate Status like, do I have to refer to the dimension Status?

if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open','closed')

If I write 'closed' instead of Status at the end of the code it doesn't work anymore.

Re: Qlik Sense: Compare dimension values to another dimension

Taoufiq_Zarra — Tue, 27 Oct 2020 10:36:34 GMT

@Oguzhan you can always do that.

if(count({<Priority=,[Order number]={"=count({<Status={'open','confirmed'}>} distinct Status)>=1"}>} Tasknumber)>=1,'open',if(Status='done','closed'))

otherwise I'll search for the best possible explanation.