Expression

[Anonymous]

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?

[devarasu07]

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]);

[woshua5550 · Accepted solution]

[Order no] as dimentsion

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

PFA

[Anonymous]

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

[woshua5550]

just add a condition in expression

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

[Anonymous]

thanks, it was useful.