topic Re: Expression in QlikView

Expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone!

I have these three tables:

Order no

100

200

300

400

javascript:;Order no	item number	Q
100	A	1
200	B	2
400	D	3
300	E	4

item number	conversion rate
A	2
B	3
D	6

I want to show the order number with no conversion rate, I mean, Order number 300.

answer:

Order no	Q
300	400

How we can do that?

Re: Expression

devarasu07 — Tue, 28 Nov 2017 06:53:37 GMT

Hi,

Try like below,

Table:
LOAD * INLINE [
    Order no
    100
    200
    300
    400
];

Table3:
LOAD * INLINE [
    item number, conversion rate
    A, 2
    B, 3
    D, 6
];
Table2:
load * Inline [
Order no, item number, Q
100, A, 1
200, B, 2
400, D, 3
300, E, 4] where not Exists([item number],[item number]);

Re: Expression

woshua5550 — Tue, 28 Nov 2017 06:57:23 GMT

[Order no] as dimentsion

Sum({<[Order no] = E({<[conversion rate]=p([conversion rate])>})>}Q) as measure

PFA

Re: Expression

Anonymous — Tue, 28 Nov 2017 10:54:33 GMT

Hi Dave Lau

thanks, it works, how about this situation, I want to add a new table , so as you can see the E and F item number does not have a conversion rate but I just would like to show type 2, therefore the answer should be F,how can I do that?

Order no

100

200

300

400

500

Order no	item number	Q
100	A	1
200	B	2
400	D	3
300	E	4
500	F	3

item number	conversion rate
A	2
B	3
D	6

item number	type
A	1
B	1
D	1
E	1
F	2

Re: Expression

woshua5550 — Tue, 28 Nov 2017 11:04:24 GMT

just add a condition in expression

Sum({<[Order no] = E({<[conversion rate]=p([conversion rate])>}),type = {2}>}Q)

Re: Expression

Anonymous — Tue, 28 Nov 2017 11:45:47 GMT

thanks, it was useful.