Is this possible with QV ?

[#1]

Hello,

i'm wondering if the situation in the attached excel-sheet is possible in QV.

Thanks.

[justinasp]

Try this:

temp1:
load *inline
[
 client, connectionnr, product, price
 10, 101, energy, "1,00"
 10, 103, electricity, "1,50"
 11, 102, energy, "1,25"
 11, 105, water, "0,75"
];
temp2:
load distinct
 client
resident temp1;
left join
load client,
 connectionnr as energy_connectionnr,
 price as energy_price
resident temp1
where product='energy';
left join
load client,
 connectionnr as electricity_connectionnr,
 price as electricity_price
resident temp1
where product='electricity';
left join
load client,
 connectionnr as water_connectionnr,
 price as water_price
resident temp1
where product='water';

[#3]

Thanks a lot !

But is it possible to do this on object-level in stead of in a script ?

Personally, I think it's not possible......

[#4]

Why wouldn't you use a pivot table with "client" and "collectionr" as dimension on the left side (vertical), "product" on top side (horizontal) and "price" as expression?

client	connectionnr	product	electricity	energy	water
10	101		-	1	-
10	103		1,5	-	-
11	102		-	1,25	-
11	105		-	-	0,75

[#5 · Accepted solution]

Even better with pivot table like this:

dimensions: "client" on left side, "product" on top side

expression: "=sum(price)", "=connectionnr"

This will provide the following display:

product	electricity	electricity	energy	energy	water	water
client	=sum(price)	=connectionnr	=sum(price)	=connectionnr	=sum(price)	=connectionnr
10	1,5	103	1	101	-	-
11	-	-	1,25	102	0,75	105

[#6]

Thanks !

It's working.....